import os
from django.shortcuts import render, HttpResponse
import json
from django.views.decorators.csrf import csrf_exempt
from tqdm import tqdm
from medb.models.models import *
import pandas as pd
import csv
from datetime import datetime

from django.core.paginator import Paginator,EmptyPage, PageNotAnInteger
from django.db.models import Q
from django.core import serializers
from django.db import transaction

file_path = r'/home/ppdb/final_basic_tables'
dippr_path = r'/home/ppdb/dippr_tables'
nistv_path = r'/home/ppdb/nist_v140_tables'

comp_convert_dict = {}

def read_comp_sei():
    comp_path = file_path+r'/A074. PCdata基表-2-组分表（20250617R1）-1版.xlsx'
    comp_df = pd.read_excel(comp_path,header=1)

    compounds_to_create = []
    synonyms_to_create = []
    
    for comp_index in comp_df.index:
        comp_row_dict = comp_df.loc[comp_index].to_dict()
        processed_comp_row_dict = {
            key: (None if pd.isna(value) else value)
            for key, value in comp_row_dict.items()
        }
        if pd.isna(processed_comp_row_dict['nPCdataCompID']):
            print('错误,组分id不可为空')
            print(comp_index,processed_comp_row_dict)
            break
        # print('当前组分id:',comp_row_dict['nPCdataCompID'])
        # if Compound.objects.filter(cpId = int(processed_comp_row_dict['nPCdataCompID'])).exists():
        #     continue
        comp = Compound(
            cpId=int(processed_comp_row_dict['nPCdataCompID']),
            cpFullname=processed_comp_row_dict['sCompName'],
            cpClass=processed_comp_row_dict['nPCdataCompType'],
            cpSMILEs=processed_comp_row_dict['sSMILES'],
            cpAlias=processed_comp_row_dict['sFormula'],
            cpCASN=processed_comp_row_dict['sCASNo'],
            cpZhFullname=processed_comp_row_dict['sCompNameCN'],
            status=1
        )
        compounds_to_create.append(comp)

        comp_synonym_str = processed_comp_row_dict['sCompNameAliasList']
        if not pd.isna(comp_synonym_str):
            comp_synonym_list = comp_synonym_str.split(';')
            for synonym in comp_synonym_list:
                if synonym.strip() != '':
                    synonyms_to_create.append(
                        CompoundSynonym(
                            csCompoundId=comp,
                            synonym=synonym.strip(),
                            status=1
                        )
                    )

    batch_size = 1000
    for i in range(0, len(compounds_to_create), batch_size):
        with transaction.atomic():
            Compound.objects.bulk_create(compounds_to_create[i:i+batch_size])

    for i in range(0, len(synonyms_to_create), batch_size):
        with transaction.atomic():
            CompoundSynonym.objects.bulk_create(synonyms_to_create[i:i+batch_size])

def read_prop_sei():
    prop_path = file_path+r'/A076B. PCdata基表-4-应用物性表（20250627R1）.xlsx'
    prop_df = pd.read_excel(prop_path,header=2)
    for prop_index in prop_df.index:
        prop_row_dict = prop_df.loc[prop_index].to_dict()
        processed_prop_row_dict = {
            key: (None if pd.isna(value) else value)
            for key, value in prop_row_dict.items()
        }
        prop_row_dict = processed_prop_row_dict.copy()
        if pd.isna(prop_row_dict['Pcdata应用物性编号']):
            print('错误,物性id不可为空')
            print(prop_index,prop_row_dict)
            break
        if prop_row_dict['应用物性类别'] == 'Others':
            continue
        # print(prop_row_dict)
        prop_sei_id = int(prop_row_dict['Pcdata应用物性编号'])
        # print('当前物性id:',int(prop_row_dict['Pcdata应用物性编号']))
        if Property.objects.filter(propId = prop_sei_id).exists():
            print('物性已存在:',prop_sei_id)
            continue
        basic_prop = None
        if not pd.isna(prop_row_dict['Pcdata物性编号']):
            basic_prop_id = str(prop_row_dict['Pcdata物性编号'])
            if BasicProp.objects.filter(sei_id = basic_prop_id).exists():
                basic_prop = BasicProp.objects.get(sei_id = basic_prop_id)
        
        prop_unit = None
        if not pd.isna(prop_row_dict['Pcdata单位编号']):
            prop_unit_name = str(prop_row_dict['Pcdata单位编号'])
            if Unit.objects.filter(sei_id=prop_unit_name).exists():
                prop_unit = Unit.objects.get(sei_id=prop_unit_name)
        
        prop_quantity = None
        if not pd.isna(prop_row_dict['量缩写']):
            prop_quantity_name = str(prop_row_dict['量缩写'])
            if Quantity.objects.filter(quantity_abbreviation__iexact=prop_quantity_name).exists():
                prop_quantity = Quantity.objects.get(quantity_abbreviation__iexact=prop_quantity_name)
        
        prop_phase1 = None
        if not pd.isna(prop_row_dict['相态1']):
            prop_phase1_name = str(prop_row_dict['相态1'])
            if Phase.objects.filter(abbreviation__iexact=prop_phase1_name).exists():
                if Phase.objects.filter(abbreviation__iexact=prop_phase1_name).count() > 1:
                    print('错误,相态重复', prop_phase1_name)
                else:
                    prop_phase1 = Phase.objects.get(abbreviation__iexact=prop_phase1_name)
        
        prop_phase2 = None
        if not pd.isna(prop_row_dict['相态2']):
            prop_phase2_name = str(prop_row_dict['相态2'])
            if Phase.objects.filter(abbreviation=prop_phase2_name).exists():
                if Phase.objects.filter(abbreviation__iexact=prop_phase2_name).count() > 1:
                    print('错误,相态重复', prop_phase2_name)
                else:
                    prop_phase2 = Phase.objects.get(abbreviation__iexact=prop_phase2_name)
                
        
        prop_phase3 = None
        if not pd.isna(prop_row_dict['相态3']):
            prop_phase3_name = str(prop_row_dict['相态3'])
            if Phase.objects.filter(abbreviation=prop_phase3_name).exists():
                if Phase.objects.filter(abbreviation__iexact=prop_phase3_name).count() > 1:
                    print('错误,相态重复', prop_phase3_name)
                else:
                    prop_phase3 = Phase.objects.get(abbreviation__iexact=prop_phase3_name)
                
        
        prop = Property()
        prop.propId = prop_sei_id
        prop.propFullname = prop_row_dict['应用物性英文名称']
        prop.propZhFullname = prop_row_dict['应用物性名称']
        prop.propClassSEI = prop_row_dict['应用物性类别']
        prop.propDescription = prop_row_dict['应用物性描述']
        # prop.propAbbname = prop_row_dict['应用物性缩写']
        prop.propBasicRef = basic_prop
        prop.propUnit = prop_unit
        prop.propQuantity = prop_quantity
        prop.propPhase1 = prop_phase1
        prop.propPhase2 = prop_phase2
        prop.propPhase3 = prop_phase3
        prop.status = 1
        prop.save()

        prop_ele_num = 1
        if not pd.isna(prop_row_dict['Aspen物性元素数量']):
            prop_ele_num = int(prop_row_dict['Aspen物性元素数量'])
        else:
            label_sym =prop_row_dict['应用物性缩写']
            new_label = LabeledValue.objects.create(lvId = -8,lvLabel = label_sym,status = 1)
            new_label.lvId = new_label.id
            new_label.save()

            new_prop_ele = PropertyElements.objects.create(peId = -8,pePropertyId = prop,peIndex = 0,peSymmetry=-1
                                                           ,peLabeledValueIJ = new_label,peLabeledValueJI = new_label,status = 1)
            new_prop_ele.peId = new_prop_ele.id
            new_prop_ele.save()
        prop.propElements = prop_ele_num
        
        prop.save()

def read_prop_lv():
    pass

def read_prop_pe():
    pass
#70纯组分，dippr覆盖aspen
def read_dippr_const_prop_value():
    eq_path = dippr_path+r'/2.Accepted Const Properties（20250624R1）.xlsx'
    if DataBanks.objects.filter(dbId = 70).exists():
        db = DataBanks.objects.filter(dbId = 70).first()
    else:
        db = DataBanks.objects.create(dbId = 70,dbName = '数据集PURE-C',dbDescription = 'PCdata纯组分物性推荐数据集-RP',status = 1)
        db.save()
    
    eq_val_df = pd.read_excel(eq_path,sheet_name='入库结构化',header=1, engine='openpyxl')
    
    compound_properties_to_create = []  # 用于批量创建 CompoundProperty 对象
    existing_cp_ids = set(CompoundProperty.objects.values_list('cpId', flat=True))  # 获取已存在的 cpId 列

    databank_compounds_to_create = []  # 用于批量创建 DataBankCompounds 对象
    databank_compounds_existing = set(DataBankCompounds.objects.values_list('dbcId', flat=True))  # 获取已存在的 dbcId 列

    dbcpdatainfo_to_create = []  # 用于批量创建 DbcpDataInfo 对象

    labeled_values_existing = set(LabeledValue.objects.values_list('lvId', flat=True))  # 获取已存在的 lvId 列

    property_elements_existing = set(PropertyElements.objects.values_list('peId', flat=True))  # 获取已存在的 peId 列

    property_value_consts_to_create = []  # 用于批量创建 PropertyValueConst 对象


    for eq_val_df_index in tqdm(eq_val_df.index, desc="Processing eq_val_df"):
        if eq_val_df_index == 0:
            continue
        comp_sei_id = eq_val_df.at[eq_val_df_index,'组分编码']
        if pd.isna(comp_sei_id) or comp_sei_id == '':
            continue
        comp_sei_id = int(comp_sei_id)

        if Compound.objects.filter(cpId = comp_sei_id).exists():
            comp = Compound.objects.filter(cpId = comp_sei_id).first()

            propfullname = eq_val_df.at[eq_val_df_index,'物性']
            prop_label = eq_val_df.at[eq_val_df_index,'物性缩写']

            if Property.objects.filter(propFullname = propfullname).exists():
                prop = Property.objects.filter(propFullname = propfullname).first()
                prop.propElements = 1
                prop.save()

                if not CompoundProperty.objects.filter(cpCompoundId=comp, cpPropertyId=prop).exists():
                # 确保 cpId 唯一性
                    max_cp_id = max(existing_cp_ids) if existing_cp_ids else 0
                    new_cp_id = max_cp_id + 1
                    existing_cp_ids.add(new_cp_id)  # 更新已存在的 cpId 集合

                    # 创建 CompoundProperty 对象
                    new_cp = CompoundProperty(
                        cpId=new_cp_id,
                        cpCompoundId=comp,
                        cpPropertyId=prop,
                        status=1
                    )
                    compound_properties_to_create.append(new_cp)
                else:
                    new_cp = CompoundProperty.objects.filter(cpCompoundId=comp, cpPropertyId=prop).first()

                if not DataBankCompounds.objects.filter(dbcDataBankId = db,dbcCompoundPropId = new_cp).exists():
                    # new_dbcp = DataBankCompounds.objects.create(dbcId = -8,dbcDataBankId = db,dbcCompoundPropId = new_cp,status = 1)
                    # new_dbcp.dbcId = new_dbcp.id
                    # if not pd.isna(eq_val_df.at[eq_val_df_index,'标注英文备注']):
                    #     new_dbcp.dbcNote = eq_val_df.at[eq_val_df_index,'标注英文备注']
                    # if not pd.isna(eq_val_df.at[eq_val_df_index,'标注中文备注']):
                    #     new_dbcp.dbcZhNote = eq_val_df.at[eq_val_df_index,'标注中文备注']
                    # new_dbcp.save()
                    max_dbcp_id = max(databank_compounds_existing) if databank_compounds_existing else 0
                    new_dbcp_id = max_dbcp_id + 1
                    databank_compounds_existing.add(new_dbcp_id)  # 更新已存在的 dbcId 集合
                    new_dbcp = DataBankCompounds(
                        dbcId=new_dbcp_id,
                        dbcDataBankId=db,
                        dbcCompoundPropId=new_cp,
                        status=1
                    )
                    if not pd.isna(eq_val_df.at[eq_val_df_index,'标注英文备注']):
                        new_dbcp.dbcNote = eq_val_df.at[eq_val_df_index,'标注英文备注']
                    if not pd.isna(eq_val_df.at[eq_val_df_index,'标注中文备注']):
                        new_dbcp.dbcZhNote = eq_val_df.at[eq_val_df_index,'标注中文备注']
                    databank_compounds_to_create.append(new_dbcp)
                else:
                    new_dbcp = DataBankCompounds.objects.filter(dbcDataBankId = db,dbcCompoundPropId = new_cp).first()
                
                if not DbcpDataInfo.objects.filter(dbcpeId = new_dbcp,status = 1).exists():
                    new_dbcpi = DbcpDataInfo(dbcpeId = new_dbcp,
                                                            dbcpeErrorSource='RP',status=1)
                    if not pd.isna(eq_val_df.at[eq_val_df_index,'误差']):
                        new_dbcpi.dbcpeErrorValue = eq_val_df.at[eq_val_df_index,'误差']
                    if not pd.isna(eq_val_df.at[eq_val_df_index,'数据类型']):
                        new_dbcpi.dbcpeDataType = eq_val_df.at[eq_val_df_index,'数据类型']
                    literature_code = eq_val_df.at[eq_val_df_index,'Pcdata引文编号']
                    if not pd.isna(literature_code) and literature_code != '':
                        if Literature.objects.filter(liter_code = literature_code).exists():
                            literature = Literature.objects.filter(liter_code = literature_code).first()
                            new_dbcpi.dbcpeLiterature = literature
                    # new_dbcpi.save()
                    dbcpdatainfo_to_create.append(new_dbcpi)

                prop_ele_list = prop.propertyelements_set.all()
                if len(prop_ele_list)>0:
                    for ele in prop_ele_list:
                        lvij = ele.peLabeledValueIJ
                        # print(lvij.lvLabel)

                        pvc_value = eq_val_df.at[eq_val_df_index,'物性值']
                        # print(pvc_value,'!!!!!!!!!!')
                        if PropertyValueConst.objects.filter(pvcCompoundPropId = new_cp,pvcLabeledValueId = lvij).exists():
                            tmp_pvc = PropertyValueConst.objects.filter(pvcCompoundPropId = new_cp,pvcLabeledValueId = lvij).first()
                            tmp_pvc.pvcValue = pvc_value
                            tmp_pvc.save()
                        else:
                            # PropertyValueConst.objects.create(pvcCompoundPropId = new_cp,pvcLabeledValueId = lvij,pvcValue = pvc_value,status = 1)
                            property_value_consts_to_create.append(
                                PropertyValueConst(
                                    pvcCompoundPropId=new_cp,
                                    pvcLabeledValueId=lvij,
                                    pvcValue=pvc_value,
                                    status=1
                                )
                            )
                else:
                    # print('没有物性元素:', propfullname)
                    # new_lv = LabeledValue.objects.create(lvId = -8,lvLabel = prop_label,status = 1)
                    # new_lv.lvId = new_lv.id
                    # new_lv.save()

                    max_lv_id = max(labeled_values_existing) if labeled_values_existing else 0
                    new_lv_id = max_lv_id + 1
                    labeled_values_existing.add(new_lv_id)  # 更新已存在的 lvId 集合
                    new_lv = LabeledValue(
                        lvId=new_lv_id,
                        lvLabel=prop_label,
                        status=1
                    )
                    new_lv.save()

                    # new_ele = PropertyElements.objects.create(peId = -8,pePropertyId = prop,peIndex = 0,peSymmetry = -1
                    #                             ,peLabeledValueIJ = new_lv,peLabeledValueJI = new_lv,status = 1)
                    # new_ele.peId = new_ele.id
                    # new_ele.save()

                    max_prop_ele_id = max(property_elements_existing) if property_elements_existing else 0
                    new_prop_ele_id = max_prop_ele_id + 1
                    property_elements_existing.add(new_prop_ele_id)  # 更新已存在的 peId 集合
                    new_ele = PropertyElements(
                        peId=new_prop_ele_id,
                        pePropertyId=prop,
                        peIndex=0,
                        peSymmetry=-1,
                        peLabeledValueIJ=new_lv,
                        peLabeledValueJI=new_lv,
                        status=1
                    )
                    new_ele.save()

                    pvc_value = eq_val_df.at[eq_val_df_index,'物性值']
                    # print(pvc_value,'!!!!!!!!!!')

                    if PropertyValueConst.objects.filter(pvcCompoundPropId = new_cp,pvcLabeledValueId = new_lv).exists():
                        tmp_pvc = PropertyValueConst.objects.filter(pvcCompoundPropId = new_cp,pvcLabeledValueId = new_lv).first()
                        tmp_pvc.pvcValue = pvc_value
                        tmp_pvc.save()
                    else:
                        property_value_consts_to_create.append(
                            PropertyValueConst(
                                pvcCompoundPropId=new_cp,
                                pvcLabeledValueId=new_lv,
                                pvcValue=pvc_value,
                                status=1
                            )
                        )
    
    # 批量创建 CompoundProperty 对象
    batch_size = 1000
    for i in range(0, len(compound_properties_to_create), batch_size):
        with transaction.atomic():
            CompoundProperty.objects.bulk_create(compound_properties_to_create[i:i+batch_size])
    
    # 批量创建 DataBankCompounds 对象
    for i in range(0, len(databank_compounds_to_create), batch_size):
        with transaction.atomic():
            DataBankCompounds.objects.bulk_create(databank_compounds_to_create[i:i+batch_size])
    
    # 批量创建 DbcpDataInfo 对象
    for i in range(0, len(dbcpdatainfo_to_create), batch_size):
        with transaction.atomic():
            DbcpDataInfo.objects.bulk_create(dbcpdatainfo_to_create[i:i+batch_size])
    
    # 批量创建 PropertyValueConst 对象
    for i in range(0, len(property_value_consts_to_create), batch_size):
        with transaction.atomic():
            PropertyValueConst.objects.bulk_create(property_value_consts_to_create[i:i+batch_size])

def read_ck_eq_comp_and_prop():
    eq_path = dippr_path+r'/4.Tdep Properties（20250616R1）.xlsx'
    # db = DataBanks.objects.filter(dbId = 70).first()

    eq_mng_df = pd.read_excel(eq_path,sheet_name='入库模型管理表')

    tmp_list = []
    for eq_mng_index in tqdm(eq_mng_df.index, desc="Processing eq_mng_df"):
        eq_short = eq_mng_df.at[eq_mng_index,'ShortName']
        if pd.isna(eq_short) or eq_short == ' ':
            continue

        prop_name = eq_mng_df.at[eq_mng_index,'ModelEnName']

        out_prop_name = eq_mng_df.at[eq_mng_index,'Property']
        out_prop = Property.objects.filter(propFullname__iexact = out_prop_name).first()

        temp_quantity = Quantity.objects.filter(quantity_abbreviation__iexact = 'TEM').first()
        temp_unit = Unit.objects.filter(sei_id = 'U-9-1').first()

        if prop_name in tmp_list:
            print(prop_name)
        else:
            tmp_list.append(prop_name)

        if Property.objects.filter(propFullname = prop_name).exists():
            # print(prop_name,' IS EXISTS')
            new_param_prop = Property.objects.filter(propFullname = prop_name).first()
            if out_prop.propBasicRef != None:
                new_param_prop.propBasicRef = out_prop.propBasicRef
                new_param_prop.save()
        else:
            
            new_param_prop = Property.objects.create(propId = -8,propFullname = prop_name,propDescription='方程'+eq_short+'的物性参数'
                        ,propIsGlobal = 0,propType = 1,propInteractionType = 0,propCompoundType = 0,status = 1)
            new_param_prop.propId = new_param_prop.id+10000
            new_param_prop.propQuantity = out_prop.propQuantity if out_prop else None
            new_param_prop.propUnit = out_prop.propUnit if out_prop else None
            if out_prop.propBasicRef != None:
                new_param_prop.propBasicRef = out_prop.propBasicRef
            new_param_prop.save()
        prop_ele_list = new_param_prop.propertyelements_set.all()
        if len(prop_ele_list) > 0:
            # print('已有物性元素:',prop_name)
            pass
        else:
            for i in range(8):
                label_str = 'Par'+str(i+1)
                label = eq_mng_df.at[eq_mng_index,label_str]
                if pd.isna(label) or i == 7:
                    new_param_prop.propElements = i
                    new_param_prop.save()
                    if pd.isna(label):
                        break
                new_lv = LabeledValue.objects.create(lvId = -8,lvLabel = label,status = 1)
                new_lv.lvId = new_lv.id
                new_lv.save()

                new_ele = PropertyElements.objects.create(peId = -8,pePropertyId = new_param_prop,peIndex = i,peSymmetry = -1
                                            ,peLabeledValueIJ = new_lv,peLabeledValueJI = new_lv,status = 1)
                new_ele.peId = new_ele.id
                new_ele.save()
        
        for i in range(1):
            var_label_str = 'Var'+str(i+1)+'Symbol'
            var_min_str = 'Var'+str(i+1)+'Min'
            var_max_str = 'Var'+str(i+1)+'Max'
            var_label = eq_mng_df.at[eq_mng_index,var_label_str]
            if pd.isna(var_label):
                break
            var_min_label = eq_mng_df.at[eq_mng_index,var_min_str]
            var_max_label = eq_mng_df.at[eq_mng_index,var_max_str]
            prop_var_name = prop_name+'_VAR'+str(i+1)
            if Property.objects.filter(propFullname = prop_var_name).exists():
                new_var_prop = Property.objects.filter(propFullname = prop_var_name).first()
                new_var_prop.propQuantity = temp_quantity if temp_quantity else None
                new_var_prop.propUnit = temp_unit if temp_unit else None
                new_var_prop.save()
            else:
                new_var_prop = Property.objects.create(propId = -8,propFullname = prop_var_name,propDescription='方程'+eq_short+'的变量'+str(i+1)+'上下限'
                            ,propElements = 2,propIsGlobal = 0,propType = 1,propInteractionType = 0,propCompoundType = 0,status = 1)
                new_var_prop.propId = new_var_prop.id+10000
                new_var_prop.propQuantity = temp_quantity if temp_quantity else None
                new_var_prop.propUnit = temp_unit if temp_unit else None
                new_var_prop.save()
            var_prop_ele_list = new_var_prop.propertyelements_set.all()
            if len(var_prop_ele_list) > 0:
                pass
            else:
                new_min_lv = LabeledValue.objects.create(lvId = -8,lvLabel = var_min_label,status = 1)
                new_min_lv.lvId = new_min_lv.id
                new_min_lv.save()

                new_min_ele = PropertyElements.objects.create(peId = -8,pePropertyId = new_var_prop,peIndex = 0,peSymmetry = -1
                                            ,peLabeledValueIJ = new_min_lv,peLabeledValueJI = new_min_lv,status = 1)
                new_min_ele.peId = new_min_ele.id
                new_min_ele.save()

                new_max_lv = LabeledValue.objects.create(lvId = -8,lvLabel = var_max_label,status = 1)
                new_max_lv.lvId = new_max_lv.id
                new_max_lv.save()

                new_max_ele = PropertyElements.objects.create(peId = -8,pePropertyId = new_var_prop,peIndex = 1,peSymmetry = -1
                                            ,peLabeledValueIJ = new_max_lv,peLabeledValueJI = new_max_lv,status = 1)
                new_max_ele.peId = new_max_ele.id
                new_max_ele.save()


#71 dippr推荐温压 72 其他温压

def read_ck_prop_value():
    eq_path = dippr_path+r'/4.Tdep Properties（20250616R1）.xlsx'
    if DataBanks.objects.filter(dbId = 71).exists():
        db = DataBanks.objects.filter(dbId = 71).first()
    else:
        db = DataBanks.objects.create(dbId = 71,dbName = '数据集RP',dbDescription = 'PCdata温压关联式推荐数据集',status = 1)
        db.save()

    if DataBanks.objects.filter(dbId = 72).exists():
        db_b = DataBanks.objects.filter(dbId = 72).first()
    else:
        db_b = DataBanks.objects.create(dbId = 72,dbName = '数据集RP-B',dbDescription = 'PCdata温压关联式其他数据集',status = 1)
        db_b.save()


    # eq_mng_df = pd.read_excel(eq_path,sheet_name='Model&ParasManagement', engine='openpyxl')
    eq_val_df = pd.read_excel(eq_path,sheet_name='入库结构化表',header=0, engine='openpyxl')

    compound_properties_to_create = []  # 用于批量创建 CompoundProperty 对象
    existing_cp_ids = set(CompoundProperty.objects.values_list('cpId', flat=True))  # 获取已存在的 cpId 列

    databank_compounds_to_create = []  # 用于批量创建 DataBankCompounds 对象
    databank_compounds_existing = set(DataBankCompounds.objects.values_list('dbcId', flat=True))  # 获取已存在的 dbcId 列

    dbcpdatainfo_to_create = []  # 用于批量创建 DbcpDataInfo 对象

    property_value_consts_to_create = []  # 用于批量创建 PropertyValueConst 对象

    # count = 0
    for eq_val_df_index in tqdm(eq_val_df.index, desc="Processing eq_mng_df"):
        # print('当前行:',eq_val_df_index)
        # print(eq_val_df_index)
        if pd.isna(eq_val_df.at[eq_val_df_index,'nPCdataCompID']) or eq_val_df.at[eq_val_df_index,'nPCdataCompID'] == ' ':
            continue
        if eq_val_df.at[eq_val_df_index,'Order'] == 'A':
            cur_db = db
        else:
            cur_db = db_b
        comp_id = eq_val_df.at[eq_val_df_index,'nPCdataCompID']
        val_en_name = eq_val_df.at[eq_val_df_index,'ModelEnName']
        
        if Compound.objects.filter(cpId = comp_id,cpAPVFlag = 1).exists():
            comp = Compound.objects.filter(cpId = comp_id,cpAPVFlag = 1).first()

            param_name = val_en_name
            if Property.objects.filter(propFullname = param_name).exists():
                param = Property.objects.filter(propFullname = param_name).first()
                # if not CompoundProperty.objects.filter(cpCompoundId = comp,cpPropertyId = param).exists():
                #     new_cp = CompoundProperty.objects.create(cpId = -8,cpCompoundId = comp,cpPropertyId = param,status = 1)
                #     new_cp.cpId = new_cp.id
                #     new_cp.save()
                # else:
                #     new_cp = CompoundProperty.objects.filter(cpCompoundId = comp,cpPropertyId = param).first()

                if not CompoundProperty.objects.filter(cpCompoundId=comp, cpPropertyId=param).exists():
                # 确保 cpId 唯一性
                    max_cp_id = max(existing_cp_ids) if existing_cp_ids else 0
                    new_cp_id = max_cp_id + 1
                    existing_cp_ids.add(new_cp_id)  # 更新已存在的 cpId 集合

                    # 创建 CompoundProperty 对象
                    new_cp = CompoundProperty(
                        cpId=new_cp_id,
                        cpCompoundId=comp,
                        cpPropertyId=param,
                        status=1
                    )
                    compound_properties_to_create.append(new_cp)
                else:
                    new_cp = CompoundProperty.objects.filter(cpCompoundId=comp, cpPropertyId=param).first()
                
                # if not DataBankCompounds.objects.filter(dbcDataBankId = cur_db,dbcCompoundPropId = new_cp).exists():
                #     new_dbcp = DataBankCompounds.objects.create(dbcId = -8,dbcDataBankId = cur_db,dbcCompoundPropId = new_cp,status = 1)
                #     new_dbcp.dbcId = new_dbcp.id
                #     new_dbcp.dbcNote = eq_val_df.at[eq_val_df_index,'sNoteEN']
                #     new_dbcp.dbcZhNote = eq_val_df.at[eq_val_df_index,'sNote']
                #     new_dbcp.save()
                # else:
                #     new_dbcp = DataBankCompounds.objects.filter(dbcDataBankId = cur_db,dbcCompoundPropId = new_cp).first()
                #     new_dbcp.dbcNote = eq_val_df.at[eq_val_df_index,'sNoteEN']
                #     new_dbcp.dbcZhNote = eq_val_df.at[eq_val_df_index,'sNote']
                #     new_dbcp.save()
                if not DataBankCompounds.objects.filter(dbcDataBankId = db,dbcCompoundPropId = new_cp).exists():
                    max_dbcp_id = max(databank_compounds_existing) if databank_compounds_existing else 0
                    new_dbcp_id = max_dbcp_id + 1
                    databank_compounds_existing.add(new_dbcp_id)  # 更新已存在的 dbcId 集合
                    new_dbcp = DataBankCompounds(
                        dbcId=new_dbcp_id,
                        dbcDataBankId=db,
                        dbcCompoundPropId=new_cp,
                        status=1
                    )
                    if not pd.isna(eq_val_df.at[eq_val_df_index,'sNoteEN']):
                        new_dbcp.dbcNote = eq_val_df.at[eq_val_df_index,'sNoteEN']
                    if not pd.isna(eq_val_df.at[eq_val_df_index,'sNote']):
                        new_dbcp.dbcZhNote = eq_val_df.at[eq_val_df_index,'sNote']
                    databank_compounds_to_create.append(new_dbcp)
                else:
                    new_dbcp = DataBankCompounds.objects.filter(dbcDataBankId = db,dbcCompoundPropId = new_cp).first()
                
                # if not DbcpDataInfo.objects.filter(dbcpeId = new_dbcp,status = 1).exists():
                #     new_dbcpi = DbcpDataInfo.objects.create(dbcpeId = new_dbcp,dbcpeErrorValue = eq_val_df.at[eq_val_df_index,'Error'],
                #                                             dbcpeErrorSource= 'RP',status = 1)
                # else:
                #     new_dbcpi = DbcpDataInfo.objects.filter(dbcpeId = new_dbcp,status = 1).first()
                #     new_dbcpi.dbcpeErrorValue = eq_val_df.at[eq_val_df_index,'Error']
                #     new_dbcpi.dbcpeErrorSource = 'RP'
                #     new_dbcpi.save()

                if not DbcpDataInfo.objects.filter(dbcpeId = new_dbcp,status = 1).exists():
                    new_dbcpi = DbcpDataInfo(dbcpeId = new_dbcp,
                                                            dbcpeErrorSource='RP',status=1)
                    if not pd.isna(eq_val_df.at[eq_val_df_index,'Error']):
                        new_dbcpi.dbcpeErrorValue = eq_val_df.at[eq_val_df_index,'Error']
                    dbcpdatainfo_to_create.append(new_dbcpi)

                prop_ele_list = param.propertyelements_set.all()
                for ele in prop_ele_list:
                    lvij = ele.peLabeledValueIJ
                    
                    ele_index = ele.peIndex+1
                    par_str = 'Par'+str(ele_index)
                    pvc_value = eq_val_df.at[eq_val_df_index,par_str]
                    if PropertyValueConst.objects.filter(pvcCompoundPropId = new_cp,pvcLabeledValueId = lvij).exists():
                        tmp_pvc = PropertyValueConst.objects.filter(pvcCompoundPropId = new_cp,pvcLabeledValueId = lvij).first()
                        tmp_pvc.pvcValue = pvc_value
                        tmp_pvc.save()
                    else:
                        # PropertyValueConst.objects.create(pvcCompoundPropId = new_cp,pvcLabeledValueId = lvij,pvcValue = pvc_value,status = 1)
                        property_value_consts_to_create.append(
                            PropertyValueConst(
                                pvcCompoundPropId=new_cp,
                                pvcLabeledValueId=lvij,
                                pvcValue=pvc_value,
                                status=1
                            )
                        )

            for i in range(1):
                var_label_str = 'Var'+str(i+1)+'Symbol'
                var_min_str = 'Var'+str(i+1)+'Min'
                var_max_str = 'Var'+str(i+1)+'Max'
                var_label = eq_val_df.at[eq_val_df_index,var_label_str]
                if pd.isna(var_label):
                    break
                var_min_val = eq_val_df.at[eq_val_df_index,var_min_str]
                var_max_val = eq_val_df.at[eq_val_df_index,var_max_str]
                prop_var_name = val_en_name+'_VAR'+str(i+1)
                if Property.objects.filter(propFullname = prop_var_name).exists():
                    var_prop = Property.objects.filter(propFullname = prop_var_name).first()
                    # if not CompoundProperty.objects.filter(cpCompoundId = comp,cpPropertyId = var_prop).exists():
                    #     new_cp = CompoundProperty.objects.create(cpId = -8,cpCompoundId = comp,cpPropertyId = var_prop,status = 1)
                    #     new_cp.cpId = new_cp.id
                    #     new_cp.save()
                    # else:
                    #     new_cp = CompoundProperty.objects.filter(cpCompoundId = comp,cpPropertyId = var_prop).first()
                    if not CompoundProperty.objects.filter(cpCompoundId=comp, cpPropertyId=var_prop).exists():
                        # 确保 cpId 唯一性
                        max_cp_id = max(existing_cp_ids) if existing_cp_ids else 0
                        new_cp_id = max_cp_id + 1
                        existing_cp_ids.add(new_cp_id)  # 更新已存在的 cpId 集合
                        # 创建 CompoundProperty 对象
                        new_cp = CompoundProperty(
                            cpId=new_cp_id,
                            cpCompoundId=comp,
                            cpPropertyId=var_prop,
                            status=1
                        )
                        compound_properties_to_create.append(new_cp)
                    else:
                        new_cp = CompoundProperty.objects.filter(cpCompoundId=comp, cpPropertyId=var_prop).first()

                    
                    # if not DataBankCompounds.objects.filter(dbcDataBankId = cur_db,dbcCompoundPropId = new_cp).exists():
                    #     new_dbcp = DataBankCompounds.objects.create(dbcId = -8,dbcDataBankId = cur_db,dbcCompoundPropId = new_cp,status = 1)
                    #     new_dbcp.dbcId = new_dbcp.id
                    #     new_dbcp.save()
                    # else:
                    #     new_dbcp = DataBankCompounds.objects.filter(dbcDataBankId = cur_db,dbcCompoundPropId = new_cp).first()
                    if not DataBankCompounds.objects.filter(dbcDataBankId = cur_db,dbcCompoundPropId = new_cp).exists():
                        max_dbcp_id = max(databank_compounds_existing) if databank_compounds_existing else 0
                        new_dbcp_id = max_dbcp_id + 1
                        databank_compounds_existing.add(new_dbcp_id)  # 更新已存在的 dbcId 集合
                        new_dbcp = DataBankCompounds(
                            dbcId=new_dbcp_id,
                            dbcDataBankId=cur_db,
                            dbcCompoundPropId=new_cp,
                            status=1
                        )
                        databank_compounds_to_create.append(new_dbcp)
                    else:
                        new_dbcp = DataBankCompounds.objects.filter(dbcDataBankId = cur_db,dbcCompoundPropId = new_cp).first()

                    var_ele_list = var_prop.propertyelements_set.all()
                    for var_ele in var_ele_list:
                        lvij = var_ele.peLabeledValueIJ
                        cur_value = None
                        if 'min' in lvij.lvLabel:
                            cur_value  = var_min_val
                        elif 'max' in lvij.lvLabel:
                            cur_value = var_max_val
                        if PropertyValueConst.objects.filter(pvcCompoundPropId = new_cp,pvcLabeledValueId = lvij).exists():
                            tmp_pvc = PropertyValueConst.objects.filter(pvcCompoundPropId = new_cp,pvcLabeledValueId = lvij).first()
                            tmp_pvc.pvcValue = cur_value
                            tmp_pvc.save()
                        else:
                            # tmp_pvc = PropertyValueConst.objects.create(pvcCompoundPropId = new_cp,pvcLabeledValueId = lvij,status = 1)
                            property_value_consts_to_create.append(
                                PropertyValueConst(
                                    pvcCompoundPropId=new_cp,
                                    pvcLabeledValueId=lvij,
                                    pvcValue=cur_value,
                                    status=1
                                )
                            )


    # 批量创建 CompoundProperty 对象
    batch_size = 1000
    for i in range(0, len(compound_properties_to_create), batch_size):
        with transaction.atomic():
            CompoundProperty.objects.bulk_create(compound_properties_to_create[i:i+batch_size])
    
    # 批量创建 DataBankCompounds 对象
    for i in range(0, len(databank_compounds_to_create), batch_size):
        with transaction.atomic():
            DataBankCompounds.objects.bulk_create(databank_compounds_to_create[i:i+batch_size])
    
    # 批量创建 DbcpDataInfo 对象
    for i in range(0, len(dbcpdatainfo_to_create), batch_size):
        with transaction.atomic():
            DbcpDataInfo.objects.bulk_create(dbcpdatainfo_to_create[i:i+batch_size])
    
    
    # 批量创建 PropertyValueConst 对象
    for i in range(0, len(property_value_consts_to_create), batch_size):
        with transaction.atomic():
            PropertyValueConst.objects.bulk_create(property_value_consts_to_create[i:i+batch_size])                
                    
    
    # print(count)

convert_dict = {'NRTL':'NRTL Binary Interaction Parameter','WILSON':'Wilson Binary Interaction Parameter','UNIQUAC':'UNIQUAC Binary Interaction Parameter'}
add_convert_dict = {'NRTL':'NBIP','WILSON':'WBIP','UNIQUAC':'UBIP'}
def read_nistv140_bin_comp_and_prop():
    eq_path = nistv_path+r'/NISTV140-二元交互作用参数-结构化-对齐版.xlsx'
    temp_quantity = Quantity.objects.filter(quantity_abbreviation__iexact = 'TEM').first()
    temp_unit = Unit.objects.filter(sei_id = 'U-9-1').first()
    frac_quantity = Quantity.objects.filter(sei_id = 'Q-8-2').first()
    frac_unit = Unit.objects.filter(sei_id = 'U-82-3').first()
    eq_mng_df = pd.read_excel(eq_path,sheet_name='binary_manage', engine='openpyxl')
    # eq_val_df = pd.read_excel(eq_path,sheet_name='NISTV140BINA',header=0, engine='openpyxl')
    for eq_mng_index in tqdm(eq_mng_df.index, desc="Processing eq_mng_df"):
        eq_short = eq_mng_df.at[eq_mng_index,'Model']
        if pd.isna(eq_short) or eq_short == ' ':
            continue
        prop_abb = add_convert_dict[eq_short]
        app_prop_name = convert_dict[eq_short]
        app_prop = Property.objects.filter(propFullname__iexact = app_prop_name).first()
        app_prop.propElements = 10
        prop_phase1 = None
        if not pd.isna(eq_mng_df.at[eq_mng_index,'Phase']):
            prop_phase1_name = str(eq_mng_df.at[eq_mng_index,'Phase'])
            if Phase.objects.filter(abbreviation__iexact=prop_phase1_name).exists():
                if Phase.objects.filter(abbreviation__iexact=prop_phase1_name).count() > 1:
                    print('错误,相态重复', prop_phase1_name)
                else:
                    prop_phase1 = Phase.objects.get(abbreviation__iexact=prop_phase1_name)
        app_prop.propPhase1 = prop_phase1
        app_prop.save()

        for i in range(2):
            var_label_str = 'Var'+str(i+1)+'Symbol'
            var_min_str = 'Var'+str(i+1)+'Min'
            var_max_str = 'Var'+str(i+1)+'Max'
            var_label = eq_mng_df.at[eq_mng_index,var_label_str]
            if pd.isna(var_label):
                break
            var_min_label = eq_mng_df.at[eq_mng_index,var_min_str]
            var_max_label = eq_mng_df.at[eq_mng_index,var_max_str]
            prop_var_name = prop_abb+'_VAR'+str(i+1)
            if Property.objects.filter(propFullname = prop_var_name).exists():
                # print(prop_var_name)
                new_var_prop = Property.objects.filter(propFullname = prop_var_name).first()
                if i == 0:
                    new_var_prop.propQuantity = frac_quantity if frac_quantity else None
                    new_var_prop.propUnit = frac_unit if frac_unit else None
                else:
                    new_var_prop.propQuantity = temp_quantity if temp_quantity else None
                    new_var_prop.propUnit = temp_unit if temp_unit else None
                new_var_prop.save()
            else:
                new_var_prop = Property.objects.create(propId = -8,propFullname = prop_var_name,propDescription='方程'+eq_short+'的变量'+str(i+1)+'上下限'
                            ,propElements = 2,propIsGlobal = 0,propType = 1,propInteractionType = 0,propCompoundType = 0,status = 1)
                new_var_prop.propId = new_var_prop.id+10000
                if i == 0:
                    new_var_prop.propQuantity = frac_quantity if frac_quantity else None
                    new_var_prop.propUnit = frac_unit if frac_unit else None
                else:
                    new_var_prop.propQuantity = temp_quantity if temp_quantity else None
                    new_var_prop.propUnit = temp_unit if temp_unit else None
                new_var_prop.save()
            var_prop_ele_list = new_var_prop.propertyelements_set.all()
            if len(var_prop_ele_list) > 0:
                # print('已有变量物性元素:',prop_var_name)
                pass
            else:
                new_min_lv = LabeledValue.objects.create(lvId = -8,lvLabel = var_min_label,status = 1)
                new_min_lv.lvId = new_min_lv.id
                new_min_lv.save()

                new_min_ele = PropertyElements.objects.create(peId = -8,pePropertyId = new_var_prop,peIndex = 0,peSymmetry = -1
                                            ,peLabeledValueIJ = new_min_lv,peLabeledValueJI = new_min_lv,status = 1)
                new_min_ele.peId = new_min_ele.id
                new_min_ele.save()

                new_max_lv = LabeledValue.objects.create(lvId = -8,lvLabel = var_max_label,status = 1)
                new_max_lv.lvId = new_max_lv.id
                new_max_lv.save()

                new_max_ele = PropertyElements.objects.create(peId = -8,pePropertyId = new_var_prop,peIndex = 1,peSymmetry = -1
                                            ,peLabeledValueIJ = new_max_lv,peLabeledValueJI = new_max_lv,status = 1)
                new_max_ele.peId = new_max_ele.id
                new_max_ele.save()


        for i in range(10):
            label_str = 'Par'+str(i+1)
            label = eq_mng_df.at[eq_mng_index,label_str]
            new_lv = LabeledValue.objects.create(lvId = -8,lvLabel = label,status = 1)
            new_lv.lvId = new_lv.id
            new_lv.save()

            new_ele = PropertyElements.objects.create(peId = -8,pePropertyId = app_prop,peIndex = i,peSymmetry = -1
                                        ,peLabeledValueIJ = new_lv,peLabeledValueJI = new_lv,status = 1)
            new_ele.peId = new_ele.id
            new_ele.save()

#2 二元交互作用参数

def read_nistv140_bin_prop_value():
    eq_path = nistv_path+r'/NISTV140-二元交互作用参数-结构化-对齐版.xlsx'
    if DataBanks.objects.filter(dbId = 2).exists():
        db = DataBanks.objects.filter(dbId = 2).first()
    else:
        db = DataBanks.objects.create(dbId = 2,dbName = '数据集BIN',dbDescription = 'PCdata二元物性参数推荐数据集',status = 1)
        db.save()
    # eq_mng_df = pd.read_excel(eq_path,sheet_name='binary_manage', engine='openpyxl')
    eq_val_df = pd.read_excel(eq_path,sheet_name='NISTV140BINA',header=0, engine='openpyxl')

    existing_cp_ids = set(Compound.objects.values_list('cpId', flat=True))  # 获取已存在的 cpId 列

    compound_properties_to_create = []  # 用于批量创建 CompoundProperty 对象
    existing_compound_properties_ids = set(CompoundProperty.objects.values_list('cpId', flat=True))  # 获取已存在的 cpId 列

    databank_compounds_to_create = []  # 用于批量创建 DataBankCompounds 对象
    databank_compounds_existing = set(DataBankCompounds.objects.values_list('dbcId', flat=True))  # 获取已存在的 dbcId 列

    # dbcpdatainfo_to_create = []  # 用于批量创建 DbcpDataInfo 对象

    property_value_consts_to_create = []  # 用于批量创建 PropertyValueConst 对象

    
    for eq_val_df_index in tqdm(eq_val_df.index, desc="Processing eq_mng_df"):
        eq_short = eq_val_df.at[eq_val_df_index,'Model']

        if pd.isna(eq_val_df.at[eq_val_df_index,'nPCdataCompID1']) or eq_val_df.at[eq_val_df_index,'nPCdataCompID1'] == '':
            continue
        if pd.isna(eq_val_df.at[eq_val_df_index,'nPCdataCompID2']) or eq_val_df.at[eq_val_df_index,'nPCdataCompID2'] == '':
            continue
        comp1_id = int(eq_val_df.at[eq_val_df_index,'nPCdataCompID1'])
        comp2_id = int(eq_val_df.at[eq_val_df_index,'nPCdataCompID2'])

        if Compound.objects.filter(cpId = comp1_id).exists() and Compound.objects.filter(cpId = comp2_id).exists():
            comp1 = Compound.objects.filter(cpId = comp1_id).first()
            comp2 = Compound.objects.filter(cpId = comp2_id).first()
            if Duos.objects.filter(duoCompound1Id = comp1,duoCompound2Id = comp2).exists():
                duo = Duos.objects.filter(duoCompound1Id = comp1,duoCompound2Id = comp2).first()
                new_duo_comp = Compound.objects.filter(cpId = duo.duoId).first()
            else:
                # new_duo_comp = Compound.objects.create(cpId = -8,status = 1)
                # new_duo_comp.cpId = new_duo_comp.id + 1000000000
                # new_duo_comp.save()
                
                max_compound_id = max(existing_cp_ids) if existing_cp_ids else 0
                new_compound_id = max_compound_id + 1
                new_duo_comp = Compound(cpId = new_compound_id,status = 1)
                # new_duo_comp = Compound(cpId = max_cp_id,status = 1)
                new_duo_comp.save()
                existing_cp_ids.add(new_duo_comp.cpId)  # 更新已存在的 cpId 集合

                # duo = Duos.objects.create(duoId = new_duo_comp.cpId,duoCompound1Id = comp1,duoCompound2Id = comp2,status = 1)
                # duo.save()

                if not Duos.objects.filter(duoId = new_duo_comp.cpId).exists():
                    duo = Duos(duoId = new_duo_comp.cpId,duoCompound1Id = comp1,duoCompound2Id = comp2,status = 1)
                    duo.save()
            
            prop_name = convert_dict[eq_short]
            prop_abb = add_convert_dict[eq_short]
            if Property.objects.filter(propFullname = prop_name).exists():
                prop = Property.objects.filter(propFullname = prop_name).first()
                # if not CompoundProperty.objects.filter(cpCompoundId = new_duo_comp,cpPropertyId = prop).exists():
                #     new_cp = CompoundProperty.objects.create(cpId = -8,cpCompoundId = new_duo_comp,cpPropertyId = prop,status = 1)
                #     new_cp.cpId = new_cp.id
                #     new_cp.save()
                # else:
                #     new_cp = CompoundProperty.objects.filter(cpCompoundId = new_duo_comp,cpPropertyId = prop).first()
                if not CompoundProperty.objects.filter(cpCompoundId=new_duo_comp, cpPropertyId=prop).exists():
                    # 确保 cpId 唯一性
                    max_cp_id = max(existing_compound_properties_ids) if existing_compound_properties_ids else 0
                    new_cp_id = max_cp_id + 1
                    existing_compound_properties_ids.add(new_cp_id)
                    # 创建 CompoundProperty 对象
                    new_cp = CompoundProperty(
                        cpId=new_cp_id,
                        cpCompoundId=new_duo_comp,
                        cpPropertyId=prop,
                        status=1
                    )
                    compound_properties_to_create.append(new_cp)
                else:
                    new_cp = CompoundProperty.objects.filter(cpCompoundId=new_duo_comp, cpPropertyId=prop).first()

                # if not DataBankCompounds.objects.filter(dbcDataBankId = db,dbcCompoundPropId = new_cp).exists():
                #     new_dbcp = DataBankCompounds.objects.create(dbcId = -8,dbcDataBankId = db,dbcCompoundPropId = new_cp,status = 1)
                #     new_dbcp.dbcId = new_dbcp.id
                #     new_dbcp.save()
                # else:
                #     new_dbcp = DataBankCompounds.objects.filter(dbcDataBankId = db,dbcCompoundPropId = new_cp).first()
                if not DataBankCompounds.objects.filter(dbcDataBankId = db,dbcCompoundPropId = new_cp).exists():
                    max_dbcp_id = max(databank_compounds_existing) if databank_compounds_existing else 0
                    new_dbcp_id = max_dbcp_id + 1
                    databank_compounds_existing.add(new_dbcp_id)  # 更新已存在的 dbcId 集合
                    new_dbcp = DataBankCompounds(
                        dbcId=new_dbcp_id,
                        dbcDataBankId=db,
                        dbcCompoundPropId=new_cp,
                        status=1
                    )
                    databank_compounds_to_create.append(new_dbcp)
                else:
                    new_dbcp = DataBankCompounds.objects.filter(dbcDataBankId = db,dbcCompoundPropId = new_cp).first()
                
                prop_ele_list = prop.propertyelements_set.all()
                for ele in prop_ele_list:
                    lvij = ele.peLabeledValueIJ
                    ele_index = ele.peIndex+1
                    par_str = 'Par'+str(ele_index)
                    pvc_value = eq_val_df.at[eq_val_df_index,par_str]
                    if PropertyValueConst.objects.filter(pvcCompoundPropId = new_cp,pvcLabeledValueId = lvij).exists():
                        tmp_pvc = PropertyValueConst.objects.filter(pvcCompoundPropId = new_cp,pvcLabeledValueId = lvij).first()
                        tmp_pvc.pvcValue = pvc_value
                        tmp_pvc.save()
                    else:
                        # PropertyValueConst.objects.create(pvcCompoundPropId = new_cp,pvcLabeledValueId = lvij,pvcValue = pvc_value,status = 1)
                        property_value_consts_to_create.append(
                            PropertyValueConst(
                                pvcCompoundPropId=new_cp,
                                pvcLabeledValueId=lvij,
                                pvcValue=pvc_value,
                                status=1
                            )
                        )
            for i in range(2):
                var_label_str = 'Var'+str(i+1)+'Symbol'
                var_min_str = 'Var'+str(i+1)+'Min'
                var_max_str = 'Var'+str(i+1)+'Max'
                var_label = eq_val_df.at[eq_val_df_index,var_label_str]
                if pd.isna(var_label):
                    break
                var_min_val = eq_val_df.at[eq_val_df_index,var_min_str]
                var_max_val = eq_val_df.at[eq_val_df_index,var_max_str]
                prop_var_name = prop_abb+'_VAR'+str(i+1)
                if Property.objects.filter(propFullname = prop_var_name).exists():
                    var_prop = Property.objects.filter(propFullname = prop_var_name).first()
                    # if not CompoundProperty.objects.filter(cpCompoundId = new_duo_comp,cpPropertyId = var_prop).exists():
                    #     new_cp = CompoundProperty.objects.create(cpId = -8,cpCompoundId = new_duo_comp,cpPropertyId = var_prop,status = 1)
                    #     new_cp.cpId = new_cp.id
                    #     new_cp.save()
                    # else:
                    #     new_cp = CompoundProperty.objects.filter(cpCompoundId = new_duo_comp,cpPropertyId = var_prop).first()
                    if not CompoundProperty.objects.filter(cpCompoundId=new_duo_comp, cpPropertyId=var_prop).exists():
                        # 确保 cpId 唯一性
                        max_cp_id = max(existing_compound_properties_ids) if existing_compound_properties_ids else 0
                        new_cp_id = max_cp_id + 1
                        existing_compound_properties_ids.add(new_cp_id)  # 更新已存在的 cpId 集合
                        # 创建 CompoundProperty 对象
                        new_cp = CompoundProperty(
                            cpId=new_cp_id,
                            cpCompoundId=new_duo_comp,
                            cpPropertyId=var_prop,
                            status=1
                        )
                        compound_properties_to_create.append(new_cp)
                    else:
                        new_cp = CompoundProperty.objects.filter(cpCompoundId=new_duo_comp, cpPropertyId=var_prop).first()
                    # if not DataBankCompounds.objects.filter(dbcDataBankId = db,dbcCompoundPropId = new_cp).exists():
                    #     new_dbcp = DataBankCompounds.objects.create(dbcId = -8,dbcDataBankId = db,dbcCompoundPropId = new_cp,status = 1)
                    #     new_dbcp.dbcId = new_dbcp.id
                    #     new_dbcp.save()
                    # else:
                    #     new_dbcp = DataBankCompounds.objects.filter(dbcDataBankId = db,dbcCompoundPropId = new_cp).first()
                    if not DataBankCompounds.objects.filter(dbcDataBankId = db,dbcCompoundPropId = new_cp).exists():
                        max_dbcp_id = max(databank_compounds_existing) if databank_compounds_existing else 0
                        new_dbcp_id = max_dbcp_id + 1
                        databank_compounds_existing.add(new_dbcp_id)  # 更新已存在的 dbcId 集合
                        new_dbcp = DataBankCompounds(
                            dbcId=new_dbcp_id,
                            dbcDataBankId=db,
                            dbcCompoundPropId=new_cp,
                            status=1
                        )
                        databank_compounds_to_create.append(new_dbcp)
                    else:
                        new_dbcp = DataBankCompounds.objects.filter(dbcDataBankId = db,dbcCompoundPropId = new_cp).first()

                    var_ele_list = var_prop.propertyelements_set.all()
                    for var_ele in var_ele_list:
                        lvij = var_ele.peLabeledValueIJ
                        # if PropertyValueConst.objects.filter(pvcCompoundPropId = new_cp,pvcLabeledValueId = lvij).exists():
                        #     tmp_pvc = PropertyValueConst.objects.filter(pvcCompoundPropId = new_cp,pvcLabeledValueId = lvij).first()
                        # else:
                        #     tmp_pvc = PropertyValueConst.objects.create(pvcCompoundPropId = new_cp,pvcLabeledValueId = lvij,status = 1)
                        
                        # if 'min' in lvij.lvLabel or 'lower' in lvij.lvLabel:
                        #     tmp_pvc.pvcValue = var_min_val
                        # elif 'max' in lvij.lvLabel or 'upper' in lvij.lvLabel:
                        #     tmp_pvc.pvcValue = var_max_val
                        # tmp_pvc.save()
                        cur_value = None
                        if 'min' in lvij.lvLabel or 'lower' in lvij.lvLabel:
                            cur_value = var_min_val
                        elif 'max' in lvij.lvLabel or 'upper' in lvij.lvLabel:
                            cur_value = var_max_val
                        
                        if PropertyValueConst.objects.filter(pvcCompoundPropId = new_cp,pvcLabeledValueId = lvij).exists():
                            tmp_pvc = PropertyValueConst.objects.filter(pvcCompoundPropId = new_cp,pvcLabeledValueId = lvij).first()
                            tmp_pvc.pvcValue = cur_value
                            tmp_pvc.save()
                        else:
                            # PropertyValueConst.objects.create(pvcCompoundPropId = new_cp,pvcLabeledValueId = lvij,pvcValue = cur_value,status = 1)
                            property_value_consts_to_create.append(
                                PropertyValueConst(
                                    pvcCompoundPropId=new_cp,
                                    pvcLabeledValueId=lvij,
                                    pvcValue=cur_value,
                                    status=1
                                )
                            )
    
            if len(compound_properties_to_create) >= 1000:
                
                batch_size = 1000
                # 批量创建 CompoundProperty 对象
                with transaction.atomic():
                    CompoundProperty.objects.bulk_create(compound_properties_to_create)
                compound_properties_to_create.clear()

                for i in range(0, len(databank_compounds_to_create), batch_size):
                    with transaction.atomic():
                        DataBankCompounds.objects.bulk_create(databank_compounds_to_create[i:i+batch_size])
                databank_compounds_to_create.clear()

                # 批量创建 PropertyValueConst 对象
                for i in range(0, len(property_value_consts_to_create), batch_size):
                    with transaction.atomic():
                        PropertyValueConst.objects.bulk_create(property_value_consts_to_create[i:i+batch_size]) 
                property_value_consts_to_create.clear()
    
    batch_size = 1000

    for i in range(0, len(compound_properties_to_create), batch_size):
        with transaction.atomic():
            CompoundProperty.objects.bulk_create(compound_properties_to_create[i:i+batch_size])
    
    # 批量创建 DataBankCompounds 对象
    for i in range(0, len(databank_compounds_to_create), batch_size):
        with transaction.atomic():
            DataBankCompounds.objects.bulk_create(databank_compounds_to_create[i:i+batch_size])
    
    # # 批量创建 DbcpDataInfo 对象
    # for i in range(0, len(dbcpdatainfo_to_create), batch_size):
    #     with transaction.atomic():
    #         DbcpDataInfo.objects.bulk_create(dbcpdatainfo_to_create[i:i+batch_size])
    
    
    # 批量创建 PropertyValueConst 对象
    for i in range(0, len(property_value_consts_to_create), batch_size):
        with transaction.atomic():
            PropertyValueConst.objects.bulk_create(property_value_consts_to_create[i:i+batch_size]) 


def read_nistv140_const_prop_label():
    eq_path = nistv_path+r'/NISTV140-指定条件下-结构化-对齐版.xlsx'

    if DataBanks.objects.filter(dbId = 70).exists():
        db = DataBanks.objects.filter(dbId = 70).first()
    else:
        db = DataBanks.objects.create(dbId = 70,dbName = '数据集PURE-C',dbDescription = 'PCdata纯组分物性推荐数据集-RP',status = 1)
        db.save()

    param_convert_df = pd.read_excel(eq_path,sheet_name='应用物性对齐', engine='openpyxl')
    param_convert_dict = {}
    for param_convert_index in param_convert_df.index:
        short_name = param_convert_df.at[param_convert_index,'ePropAbbreviation']
        sei_name = param_convert_df.at[param_convert_index,'eAppPropNameEN']
        param_convert_dict[short_name] = sei_name
    eq_mng_df = pd.read_excel(eq_path,sheet_name='汇总表', engine='openpyxl',header=1)

    labeled_values_existing = set(LabeledValue.objects.values_list('lvId', flat=True))  # 获取已存在的 lvId 列

    property_elements_existing = set(PropertyElements.objects.values_list('peId', flat=True))  # 获取已存在的 peId 列

    already_property = []

    for eq_mng_index in tqdm(eq_mng_df.index, desc="Processing eq_mng_df"):
        if eq_mng_index == 0:
            continue
        comp_sei_id = eq_mng_df.at[eq_mng_index,'组分编码']
        if pd.isna(comp_sei_id) or comp_sei_id == '':
            continue
        comp_sei_id = int(comp_sei_id)
        if Compound.objects.filter(cpId = comp_sei_id).exists():
            prop_abb_name = eq_mng_df.at[eq_mng_index,'应用物性缩写']

            propfullname = param_convert_dict.get(prop_abb_name, None)
            if propfullname is None:
                print('未找到应用物性缩写:', prop_abb_name)
                continue
            elif propfullname in already_property:
                continue
            if Property.objects.filter(propFullname = propfullname).exists():
                prop = Property.objects.filter(propFullname = propfullname).first()
                prop_ele_list = prop.propertyelements_set.all()
                
                if len(prop_ele_list)>0:
                    continue
                else:
                    max_lv_id = max(labeled_values_existing) if labeled_values_existing else 0
                    new_lv_id = max_lv_id + 1
                    labeled_values_existing.add(new_lv_id)  # 更新已存在的 lvId 集合
                    new_lv = LabeledValue(
                        lvId=new_lv_id,
                        lvLabel=eq_mng_df.at[eq_mng_index,'应用物性符号'],
                        status=1
                    )
                    new_lv.save()

                    max_prop_ele_id = max(property_elements_existing) if property_elements_existing else 0
                    new_prop_ele_id = max_prop_ele_id + 1
                    property_elements_existing.add(new_prop_ele_id)  # 更新已存在的 peId 集合
                    new_ele = PropertyElements(
                        peId=new_prop_ele_id,
                        pePropertyId=prop,
                        peIndex=0,
                        peSymmetry=-1,
                        peLabeledValueIJ=new_lv,
                        peLabeledValueJI=new_lv,
                        status=1
                    )
                    new_ele.save()

                    already_property.append(propfullname)

def read_nistv140_const_prop_value():
    eq_path = nistv_path+r'/NISTV140-指定条件下-结构化-对齐版.xlsx'

    if DataBanks.objects.filter(dbId = 70).exists():
        db = DataBanks.objects.filter(dbId = 70).first()
    else:
        db = DataBanks.objects.create(dbId = 70,dbName = '数据集PURE-C',dbDescription = 'PCdata纯组分物性推荐数据集-RP',status = 1)
        db.save()

    param_convert_df = pd.read_excel(eq_path,sheet_name='应用物性对齐', engine='openpyxl')
    param_convert_dict = {}
    for param_convert_index in param_convert_df.index:
        short_name = param_convert_df.at[param_convert_index,'ePropAbbreviation']
        sei_name = param_convert_df.at[param_convert_index,'eAppPropNameEN']
        param_convert_dict[short_name] = sei_name
    eq_mng_df = pd.read_excel(eq_path,sheet_name='汇总表', engine='openpyxl',header=1)

    compound_properties_to_create = []  # 用于批量创建 CompoundProperty 对象
    existing_cp_ids = set(CompoundProperty.objects.values_list('cpId', flat=True))  # 获取已存在的 cpId 列

    databank_compounds_to_create = []  # 用于批量创建 DataBankCompounds 对象
    databank_compounds_existing = set(DataBankCompounds.objects.values_list('dbcId', flat=True))  # 获取已存在的 dbcId 列

    dbcpdatainfo_to_create = []  # 用于批量创建 DbcpDataInfo 对象

    property_value_consts_to_create = []  # 用于批量创建 PropertyValueConst 对象

    # eq_val_df = pd.read_excel(eq_path,sheet_name='NISTV140BINA',header=0, engine='openpyxl')
    for eq_mng_index in tqdm(eq_mng_df.index, desc="Processing eq_mng_df"):
        if eq_mng_index == 0:
            continue
        comp_sei_id = eq_mng_df.at[eq_mng_index,'组分编码']
        if pd.isna(comp_sei_id) or comp_sei_id == '':
            continue
        comp_sei_id = int(comp_sei_id)
        if Compound.objects.filter(cpId = comp_sei_id).exists():
            comp = Compound.objects.filter(cpId = comp_sei_id).first()
            prop_abb_name = eq_mng_df.at[eq_mng_index,'应用物性缩写']

            propfullname = param_convert_dict.get(prop_abb_name, None)
            if propfullname is None:
                print('未找到应用物性缩写:', prop_abb_name)
                continue
            if Property.objects.filter(propFullname = propfullname).exists():
                prop = Property.objects.filter(propFullname = propfullname).first()

                if not CompoundProperty.objects.filter(cpCompoundId=comp, cpPropertyId=prop).exists():
                # 确保 cpId 唯一性
                    max_cp_id = max(existing_cp_ids) if existing_cp_ids else 0
                    new_cp_id = max_cp_id + 1
                    existing_cp_ids.add(new_cp_id)  # 更新已存在的 cpId 集合

                    # 创建 CompoundProperty 对象
                    new_cp = CompoundProperty(
                        cpId=new_cp_id,
                        cpCompoundId=comp,
                        cpPropertyId=prop,
                        status=1
                    )
                    compound_properties_to_create.append(new_cp)
                else:
                    new_cp = CompoundProperty.objects.filter(cpCompoundId=comp, cpPropertyId=prop).first()

                if not DataBankCompounds.objects.filter(dbcDataBankId = db,dbcCompoundPropId = new_cp).exists():
                    max_dbcp_id = max(databank_compounds_existing) if databank_compounds_existing else 0
                    new_dbcp_id = max_dbcp_id + 1
                    databank_compounds_existing.add(new_dbcp_id)  # 更新已存在的 dbcId 集合
                    new_dbcp = DataBankCompounds(
                        dbcId=new_dbcp_id,
                        dbcDataBankId=db,
                        dbcCompoundPropId=new_cp,
                        status=1
                    )
                    if not pd.isna(eq_mng_df.at[eq_mng_index,'标注英文备注']):
                        new_dbcp.dbcNote = eq_mng_df.at[eq_mng_index,'标注英文备注']
                    databank_compounds_to_create.append(new_dbcp)
                else:
                    new_dbcp = DataBankCompounds.objects.filter(dbcDataBankId = db,dbcCompoundPropId = new_cp).first()
                
                if not DbcpDataInfo.objects.filter(dbcpeId = new_dbcp,status = 1).exists():
                    new_dbcpi = DbcpDataInfo(dbcpeId = new_dbcp,
                                                            dbcpeErrorSource='RP',status=1)
                    if not pd.isna(eq_mng_df.at[eq_mng_index,'误差值']):
                        new_dbcpi.dbcpeErrorValue = eq_mng_df.at[eq_mng_index,'误差值']
                    literature_code = eq_mng_df.at[eq_mng_index,'PCdata引文编号']
                    if not pd.isna(literature_code) and literature_code != '':
                        if Literature.objects.filter(liter_code = literature_code).exists():
                            literature = Literature.objects.filter(liter_code = literature_code).first()
                            new_dbcpi.dbcpeLiterature = literature
                    # new_dbcpi.save()
                    dbcpdatainfo_to_create.append(new_dbcpi)

                prop_ele_list = prop.propertyelements_set.all()
                if len(prop_ele_list)>0:
                    for ele in prop_ele_list:
                        lvij = ele.peLabeledValueIJ
                        # print(lvij.lvLabel)

                        pvc_value = eq_mng_df.at[eq_mng_index,'物性值']
                        # print(pvc_value,'!!!!!!!!!!')
                        if PropertyValueConst.objects.filter(pvcCompoundPropId = new_cp,pvcLabeledValueId = lvij).exists():
                            tmp_pvc = PropertyValueConst.objects.filter(pvcCompoundPropId = new_cp,pvcLabeledValueId = lvij).first()
                            tmp_pvc.pvcValue = pvc_value
                            tmp_pvc.save()
                        else:
                            # PropertyValueConst.objects.create(pvcCompoundPropId = new_cp,pvcLabeledValueId = lvij,pvcValue = pvc_value,status = 1)
                            property_value_consts_to_create.append(
                                PropertyValueConst(
                                    pvcCompoundPropId=new_cp,
                                    pvcLabeledValueId=lvij,
                                    pvcValue=pvc_value,
                                    status=1
                                )
                            )

                if len(compound_properties_to_create) >= 1000:
                    batch_size = 1000
                    
                    # 批量创建 CompoundProperty 对象
                    for i in range(0, len(compound_properties_to_create), batch_size):
                        with transaction.atomic():
                            CompoundProperty.objects.bulk_create(compound_properties_to_create[i:i+batch_size])
                    compound_properties_to_create.clear()

                    # 批量创建 DataBankCompounds 对象
                    for i in range(0, len(databank_compounds_to_create), batch_size):
                        with transaction.atomic():
                            DataBankCompounds.objects.bulk_create(databank_compounds_to_create[i:i+batch_size])
                    databank_compounds_to_create.clear()

                    # 批量创建 DbcpDataInfo 对象
                    for i in range(0, len(dbcpdatainfo_to_create), batch_size):
                        with transaction.atomic():
                            DbcpDataInfo.objects.bulk_create(dbcpdatainfo_to_create[i:i+batch_size])
                    dbcpdatainfo_to_create.clear()

                    # 批量创建 PropertyValueConst 对象
                    for i in range(0, len(property_value_consts_to_create), batch_size):
                        with transaction.atomic():
                            PropertyValueConst.objects.bulk_create(property_value_consts_to_create[i:i+batch_size])
                    property_value_consts_to_create.clear()

    batch_size = 1000
    for i in range(0, len(compound_properties_to_create), batch_size):
        with transaction.atomic():
            CompoundProperty.objects.bulk_create(compound_properties_to_create[i:i+batch_size])
    
    # 批量创建 DataBankCompounds 对象
    for i in range(0, len(databank_compounds_to_create), batch_size):
        with transaction.atomic():
            DataBankCompounds.objects.bulk_create(databank_compounds_to_create[i:i+batch_size])
    
    # 批量创建 DbcpDataInfo 对象
    for i in range(0, len(dbcpdatainfo_to_create), batch_size):
        with transaction.atomic():
            DbcpDataInfo.objects.bulk_create(dbcpdatainfo_to_create[i:i+batch_size])
    
    # 批量创建 PropertyValueConst 对象
    for i in range(0, len(property_value_consts_to_create), batch_size):
        with transaction.atomic():
            PropertyValueConst.objects.bulk_create(property_value_consts_to_create[i:i+batch_size])

def read_nistv140_tdep_comp_and_prop():
    eq_path = nistv_path+r'/NISTV140-温度关联式-结构化-对齐版.xlsx'
    eq_mng_df = pd.read_excel(eq_path,sheet_name='模型参数结构表', engine='openpyxl')

    temp_quantity = Quantity.objects.filter(quantity_abbreviation__iexact = 'TEM').first()
    temp_unit = Unit.objects.filter(sei_id = 'U-9-1').first()

    # param_convert_df = pd.read_excel(eq_path,sheet_name='应用物性对齐', engine='openpyxl')
    # param_convert_dict = {}
    # for param_convert_index in param_convert_df.index:
    #     short_name = param_convert_df.at[param_convert_index,'ePropAbbreviation']
    #     sei_name = param_convert_df.at[param_convert_index,'eAppPropNameEN']
    #     param_convert_dict[short_name] = sei_name
    tmp_list = []
    for eq_mng_index in tqdm(eq_mng_df.index, desc="Processing eq_mng_df"):
        prop_name = eq_mng_df.at[eq_mng_index,'eAppPropNameEN']
        eq_short = eq_mng_df.at[eq_mng_index,'ModelEnName']
        if pd.isna(prop_name) or prop_name == '':
            continue

        if prop_name in tmp_list:
            print(prop_name,' IS EXISTS')
            continue
        else:
            tmp_list.append(prop_name)
        
        if Property.objects.filter(propFullname = prop_name).exists():
            new_param_prop = Property.objects.filter(propFullname = prop_name).first()
            prop_ele_list = new_param_prop.propertyelements_set.all()
            if len(prop_ele_list)>0:
                continue
            else:
                for i in range(11):
                    label_str = 'PAR'+str(i+1)
                    label = eq_mng_df.at[eq_mng_index,label_str]
                    if pd.isna(label) or i == 10:
                        new_param_prop.propElements = i
                        new_param_prop.save()
                        if pd.isna(label):
                            break
                    new_lv = LabeledValue.objects.create(lvId = -8,lvLabel = label,status = 1)
                    new_lv.lvId = new_lv.id
                    new_lv.save()

                    new_ele = PropertyElements.objects.create(peId = -8,pePropertyId = new_param_prop,peIndex = i,peSymmetry = -1
                                                ,peLabeledValueIJ = new_lv,peLabeledValueJI = new_lv,status = 1)
                    new_ele.peId = new_ele.id
                    new_ele.save()

        for i in range(1):
            var_label_str = 'Var'+str(i+1)+'Symbol'
            var_min_str = 'Var'+str(i+1)+'Min'
            var_max_str = 'Var'+str(i+1)+'Max'
            var_label = eq_mng_df.at[eq_mng_index,var_label_str]
            if pd.isna(var_label):
                break
            var_min_label = eq_mng_df.at[eq_mng_index,var_min_str]
            var_max_label = eq_mng_df.at[eq_mng_index,var_max_str]
            prop_var_name = prop_name+'_VAR'+str(i+1)
            if Property.objects.filter(propFullname = prop_var_name).exists():
                # print(prop_var_name)
                new_var_prop = Property.objects.filter(propFullname = prop_var_name).first()
                new_var_prop.propQuantity = temp_quantity if temp_quantity else None
                new_var_prop.propUnit = temp_unit if temp_unit else None
                new_var_prop.save()
            else:
                new_var_prop = Property.objects.create(propId = -8,propFullname = prop_var_name,propDescription='方程'+eq_short+'的变量'+str(i+1)+'上下限'
                            ,propElements = 2,propIsGlobal = 0,propType = 1,propInteractionType = 0,propCompoundType = 0,status = 1)
                new_var_prop.propId = new_var_prop.id+10000
                new_var_prop.propQuantity = temp_quantity if temp_quantity else None
                new_var_prop.propUnit = temp_unit if temp_unit else None
                new_var_prop.save()

            new_var_prop_ele_list = new_var_prop.propertyelements_set.all()
            if len(new_var_prop_ele_list) == 0:
                new_min_lv = LabeledValue.objects.create(lvId = -8,lvLabel = var_min_label,status = 1)
                new_min_lv.lvId = new_min_lv.id
                new_min_lv.save()

                new_min_ele = PropertyElements.objects.create(peId = -8,pePropertyId = new_var_prop,peIndex = 0,peSymmetry = -1
                                            ,peLabeledValueIJ = new_min_lv,peLabeledValueJI = new_min_lv,status = 1)
                new_min_ele.peId = new_min_ele.id
                new_min_ele.save()

                new_max_lv = LabeledValue.objects.create(lvId = -8,lvLabel = var_max_label,status = 1)
                new_max_lv.lvId = new_max_lv.id
                new_max_lv.save()

                new_max_ele = PropertyElements.objects.create(peId = -8,pePropertyId = new_var_prop,peIndex = 1,peSymmetry = -1
                                            ,peLabeledValueIJ = new_max_lv,peLabeledValueJI = new_max_lv,status = 1)
                new_max_ele.peId = new_max_ele.id
                new_max_ele.save()

def read_nistv140_tdep_prop_value():
    eq_path = nistv_path+r'/NISTV140-温度关联式-结构化-对齐版.xlsx'
    eq_val_df = pd.read_excel(eq_path,sheet_name='汇总表', engine='openpyxl')

    param_convert_df = pd.read_excel(eq_path,sheet_name='应用物性对齐', engine='openpyxl')
    param_convert_dict = {}
    for param_convert_index in param_convert_df.index:
        short_name = param_convert_df.at[param_convert_index,'ePropAbbreviation']
        sei_name = param_convert_df.at[param_convert_index,'eAppPropNameEN']
        param_convert_dict[short_name] = sei_name

    if DataBanks.objects.filter(dbId = 3).exists():
        db = DataBanks.objects.filter(dbId = 3).first()
    else:
        db = DataBanks.objects.create(dbId = 3,dbName = '数据集TDEP',dbDescription = 'PCdata温压关联式推荐数据集-NV',status = 1)
        db.save()

    compound_properties_to_create = []  # 用于批量创建 CompoundProperty 对象
    existing_cp_ids = set(CompoundProperty.objects.values_list('cpId', flat=True))  # 获取已存在的 cpId 列

    databank_compounds_to_create = []  # 用于批量创建 DataBankCompounds 对象
    databank_compounds_existing = set(DataBankCompounds.objects.values_list('dbcId', flat=True))  # 获取已存在的 dbcId 列

    dbcpdatainfo_to_create = []  # 用于批量创建 DbcpDataInfo 对象

    property_value_consts_to_create = []  # 用于批量创建 PropertyValueConst 对象

    for eq_val_df_index in tqdm(eq_val_df.index, desc="Processing eq_mng_df"):
        # print('当前行:',eq_val_df_index)
        comp_sei_id = eq_val_df.at[eq_val_df_index,'nPCdataCompID']
        if pd.isna(comp_sei_id) or comp_sei_id == '':
            continue
        comp_sei_id = int(comp_sei_id)
        if Compound.objects.filter(cpId = comp_sei_id).exists():
            comp = Compound.objects.filter(cpId = comp_sei_id).first()
            prop_abb_name = eq_val_df.at[eq_val_df_index,'ePropAbbreviation']
            propfullname = param_convert_dict.get(prop_abb_name, None)
            if propfullname is None:
                print('未找到应用物性缩写:', prop_abb_name)
                continue
            if Property.objects.filter(propFullname = propfullname).exists():
                param = Property.objects.filter(propFullname = propfullname).first()
                # if not CompoundProperty.objects.filter(cpCompoundId = comp,cpPropertyId = param).exists():
                #     new_cp = CompoundProperty.objects.create(cpId = -8,cpCompoundId = comp,cpPropertyId = param,status = 1)
                #     new_cp.cpId = new_cp.id
                #     new_cp.save()
                # else:
                #     new_cp = CompoundProperty.objects.filter(cpCompoundId = comp,cpPropertyId = param).first()
                
                if not CompoundProperty.objects.filter(cpCompoundId=comp, cpPropertyId=param).exists():
                    # 确保 cpId 唯一性
                    max_cp_id = max(existing_cp_ids) if existing_cp_ids else 0
                    new_cp_id = max_cp_id + 1
                    existing_cp_ids.add(new_cp_id)  # 更新已存在的 cpId 集合
                    # 创建 CompoundProperty 对象
                    new_cp = CompoundProperty(
                        cpId=new_cp_id,
                        cpCompoundId=comp,
                        cpPropertyId=param,
                        status=1
                    )
                    compound_properties_to_create.append(new_cp)
                else:
                    new_cp = CompoundProperty.objects.filter(cpCompoundId=comp, cpPropertyId=param).first()
                
                # if not DataBankCompounds.objects.filter(dbcDataBankId = db,dbcCompoundPropId = new_cp).exists():
                #     new_dbcp = DataBankCompounds.objects.create(dbcId = -8,dbcDataBankId = db,dbcCompoundPropId = new_cp,status = 1)
                #     new_dbcp.dbcId = new_dbcp.id
                #     new_dbcp.dbcNote = eq_val_df.at[eq_val_df_index,'Note']
                #     new_dbcp.save()
                # else:
                #     new_dbcp = DataBankCompounds.objects.filter(dbcDataBankId = db,dbcCompoundPropId = new_cp).first()
                #     new_dbcp.dbcNote = eq_val_df.at[eq_val_df_index,'Note']
                #     new_dbcp.save()
                if not DataBankCompounds.objects.filter(dbcDataBankId = db,dbcCompoundPropId = new_cp).exists():
                    max_dbcp_id = max(databank_compounds_existing) if databank_compounds_existing else 0
                    new_dbcp_id = max_dbcp_id + 1
                    databank_compounds_existing.add(new_dbcp_id)  # 更新已存在的 dbcId 集合
                    new_dbcp = DataBankCompounds(
                        dbcId=new_dbcp_id,
                        dbcDataBankId=db,
                        dbcCompoundPropId=new_cp,
                        status=1
                    )
                    if not pd.isna(eq_val_df.at[eq_val_df_index,'Note']):
                        new_dbcp.dbcNote = eq_val_df.at[eq_val_df_index,'Note']
                    databank_compounds_to_create.append(new_dbcp)
                else:
                    new_dbcp = DataBankCompounds.objects.filter(dbcDataBankId = db,dbcCompoundPropId = new_cp).first()
                    new_dbcp.dbcNote = eq_val_df.at[eq_val_df_index,'Note']
                    new_dbcp.save()

                # if not DbcpDataInfo.objects.filter(dbcpeId = new_dbcp,status = 1).exists():
                #     new_dbcpi = DbcpDataInfo.objects.create(dbcpeId = new_dbcp,
                #                                             dbcpeErrorSource='TDEP-NV',status=1)
                #     literature_code = eq_val_df.at[eq_val_df_index,'nPCdataCitID']
                #     if not pd.isna(literature_code) and literature_code != '':
                #         if Literature.objects.filter(liter_code = literature_code).exists():
                #             literature = Literature.objects.filter(liter_code = literature_code).first()
                #             new_dbcpi.dbcpeLiterature = literature
                #     new_dbcpi.save()
                # else:
                #     new_dbcpi = DbcpDataInfo.objects.filter(dbcpeId = new_dbcp,status = 1).first()
                #     literature_code = eq_val_df.at[eq_val_df_index,'nPCdataCitID']
                #     if not pd.isna(literature_code) and literature_code != '':
                #         if Literature.objects.filter(liter_code = literature_code).exists():
                #             literature = Literature.objects.filter(liter_code = literature_code).first()
                #             new_dbcpi.dbcpeLiterature = literature
                #     new_dbcpi.save()
                if not DbcpDataInfo.objects.filter(dbcpeId = new_dbcp,status = 1).exists():
                    new_dbcpi = DbcpDataInfo(dbcpeId = new_dbcp,
                                                            dbcpeErrorSource='TDEP-NV',status=1)
                    literature_code = eq_val_df.at[eq_val_df_index,'nPCdataCitID']
                    if not pd.isna(literature_code) and literature_code != '':
                        if Literature.objects.filter(liter_code = literature_code).exists():
                            literature = Literature.objects.filter(liter_code = literature_code).first()
                            new_dbcpi.dbcpeLiterature = literature
                    dbcpdatainfo_to_create.append(new_dbcpi)
                else:
                    new_dbcpi = DbcpDataInfo.objects.filter(dbcpeId = new_dbcp,status = 1).first()
                    literature_code = eq_val_df.at[eq_val_df_index,'nPCdataCitID']
                    if not pd.isna(literature_code) and literature_code != '':
                        if Literature.objects.filter(liter_code = literature_code).exists():
                            literature = Literature.objects.filter(liter_code = literature_code).first()
                            new_dbcpi.dbcpeLiterature = literature
                    new_dbcpi.save()
                
                prop_ele_list = param.propertyelements_set.all()
                for ele in prop_ele_list:
                    lvij = ele.peLabeledValueIJ
                    # print(lvij.lvLabel)
                    ele_index = ele.peIndex+1
                    par_str = 'PAR'+str(ele_index)
                    pvc_value = eq_val_df.at[eq_val_df_index,par_str]
                    # print(pvc_value,'!!!!!!!!!!')
                    if PropertyValueConst.objects.filter(pvcCompoundPropId = new_cp,pvcLabeledValueId = lvij).exists():
                        tmp_pvc = PropertyValueConst.objects.filter(pvcCompoundPropId = new_cp,pvcLabeledValueId = lvij).first()
                        tmp_pvc.pvcValue = pvc_value
                        tmp_pvc.save()
                    else:
                        # PropertyValueConst.objects.create(pvcCompoundPropId = new_cp,pvcLabeledValueId = lvij,pvcValue = pvc_value,status = 1)
                        property_value_consts_to_create.append(
                            PropertyValueConst(
                                pvcCompoundPropId=new_cp,
                                pvcLabeledValueId=lvij,
                                pvcValue=pvc_value,
                                status=1
                            )
                        )
                

            for i in range(1):
                var_label_str = 'Var'+str(i+1)+'Symbol'
                var_min_str = 'Var'+str(i+1)+'Min'
                var_max_str = 'Var'+str(i+1)+'Max'
                var_label = eq_val_df.at[eq_val_df_index,var_label_str]
                if pd.isna(var_label):
                    break
                var_min_val = eq_val_df.at[eq_val_df_index,var_min_str]
                var_max_val = eq_val_df.at[eq_val_df_index,var_max_str]
                prop_var_name = propfullname+'_VAR'+str(i+1)
                if Property.objects.filter(propFullname = prop_var_name).exists():
                    var_prop = Property.objects.filter(propFullname = prop_var_name).first()
                    # if not CompoundProperty.objects.filter(cpCompoundId = comp,cpPropertyId = var_prop).exists():
                    #     new_cp = CompoundProperty.objects.create(cpId = -8,cpCompoundId = comp,cpPropertyId = var_prop,status = 1)
                    #     new_cp.cpId = new_cp.id
                    #     new_cp.save()
                    # else:
                    #     new_cp = CompoundProperty.objects.filter(cpCompoundId = comp,cpPropertyId = var_prop).first()
                    if not CompoundProperty.objects.filter(cpCompoundId=comp, cpPropertyId=var_prop).exists():
                        # 确保 cpId 唯一性
                        max_cp_id = max(existing_cp_ids) if existing_cp_ids else 0
                        new_cp_id = max_cp_id + 1
                        existing_cp_ids.add(new_cp_id)  # 更新已存在的 cpId 集合
                        # 创建 CompoundProperty 对象
                        new_cp = CompoundProperty(
                            cpId=new_cp_id,
                            cpCompoundId=comp,
                            cpPropertyId=var_prop,
                            status=1
                        )
                        compound_properties_to_create.append(new_cp)
                    else:
                        new_cp = CompoundProperty.objects.filter(cpCompoundId=comp, cpPropertyId=var_prop).first()
                    # if not DataBankCompounds.objects.filter(dbcDataBankId = db,dbcCompoundPropId = new_cp).exists():
                    #     new_dbcp = DataBankCompounds.objects.create(dbcId = -8,dbcDataBankId = db,dbcCompoundPropId = new_cp,status = 1)
                    #     new_dbcp.dbcId = new_dbcp.id
                    #     new_dbcp.save()
                    # else:
                    #     new_dbcp = DataBankCompounds.objects.filter(dbcDataBankId = db,dbcCompoundPropId = new_cp).first()
                    if not DataBankCompounds.objects.filter(dbcDataBankId = db,dbcCompoundPropId = new_cp).exists():
                        max_dbcp_id = max(databank_compounds_existing) if databank_compounds_existing else 0
                        new_dbcp_id = max_dbcp_id + 1
                        databank_compounds_existing.add(new_dbcp_id)
                        new_dbcp = DataBankCompounds(
                            dbcId=new_dbcp_id,
                            dbcDataBankId=db,
                            dbcCompoundPropId=new_cp,
                            status=1
                        )
                        databank_compounds_to_create.append(new_dbcp)
                    else:
                        new_dbcp = DataBankCompounds.objects.filter(dbcDataBankId = db,dbcCompoundPropId = new_cp).first()
                    var_ele_list = var_prop.propertyelements_set.all()
                    for var_ele in var_ele_list:
                        lvij = var_ele.peLabeledValueIJ
                        # if PropertyValueConst.objects.filter(pvcCompoundPropId = new_cp,pvcLabeledValueId = lvij).exists():
                        #     tmp_pvc = PropertyValueConst.objects.filter(pvcCompoundPropId = new_cp,pvcLabeledValueId = lvij).first()
                        # else:
                        #     tmp_pvc = PropertyValueConst.objects.create(pvcCompoundPropId = new_cp,pvcLabeledValueId = lvij,status = 1)
                        
                        # if 'min' in lvij.lvLabel:
                        #     tmp_pvc.pvcValue = var_min_val
                        # elif 'max' in lvij.lvLabel:
                        #     tmp_pvc.pvcValue = var_max_val
                        # tmp_pvc.save()
                        cur_value = None
                        if 'min' in lvij.lvLabel:
                            cur_value = var_min_val
                        elif 'max' in lvij.lvLabel:
                            cur_value = var_max_val
                        if PropertyValueConst.objects.filter(pvcCompoundPropId = new_cp,pvcLabeledValueId = lvij).exists():
                            tmp_pvc = PropertyValueConst.objects.filter(pvcCompoundPropId = new_cp,pvcLabeledValueId = lvij).first()
                            tmp_pvc.pvcValue = cur_value
                            tmp_pvc.save()
                        else:
                            # PropertyValueConst.objects.create(pvcCompoundPropId = new_cp,pvcLabeledValueId = lvij,pvcValue = cur_value,status = 1)
                            property_value_consts_to_create.append(
                                PropertyValueConst(
                                    pvcCompoundPropId=new_cp,
                                    pvcLabeledValueId=lvij,
                                    pvcValue=cur_value,
                                    status=1
                                )
                            )

            if len(compound_properties_to_create) >= 1000:
                batch_size = 1000
                
                # 批量创建 CompoundProperty 对象
                for i in range(0, len(compound_properties_to_create), batch_size):
                    with transaction.atomic():
                        CompoundProperty.objects.bulk_create(compound_properties_to_create[i:i+batch_size])
                compound_properties_to_create.clear()

                # 批量创建 DataBankCompounds 对象
                for i in range(0, len(databank_compounds_to_create), batch_size):
                    with transaction.atomic():
                        DataBankCompounds.objects.bulk_create(databank_compounds_to_create[i:i+batch_size])
                databank_compounds_to_create.clear()

                # 批量创建 DbcpDataInfo 对象
                for i in range(0, len(dbcpdatainfo_to_create), batch_size):
                    with transaction.atomic():
                        DbcpDataInfo.objects.bulk_create(dbcpdatainfo_to_create[i:i+batch_size])
                dbcpdatainfo_to_create.clear()

                # 批量创建 PropertyValueConst 对象
                for i in range(0, len(property_value_consts_to_create), batch_size):
                    with transaction.atomic():
                        PropertyValueConst.objects.bulk_create(property_value_consts_to_create[i:i+batch_size])
                property_value_consts_to_create.clear()
    # 批量创建 CompoundProperty 对象
    batch_size = 1000
    for i in range(0, len(compound_properties_to_create), batch_size):
        with transaction.atomic():
            CompoundProperty.objects.bulk_create(compound_properties_to_create[i:i+batch_size])
    
    # 批量创建 DataBankCompounds 对象
    for i in range(0, len(databank_compounds_to_create), batch_size):
        with transaction.atomic():
            DataBankCompounds.objects.bulk_create(databank_compounds_to_create[i:i+batch_size])
    
    # 批量创建 DbcpDataInfo 对象
    for i in range(0, len(dbcpdatainfo_to_create), batch_size):
        with transaction.atomic():
            DbcpDataInfo.objects.bulk_create(dbcpdatainfo_to_create[i:i+batch_size])
    
    
    # 批量创建 PropertyValueConst 对象
    for i in range(0, len(property_value_consts_to_create), batch_size):
        with transaction.atomic():
            PropertyValueConst.objects.bulk_create(property_value_consts_to_create[i:i+batch_size]) 



def read_nistv140_tdep_prop_value_accelerated():
    eq_path = nistv_path+r'/NISTV140-温度关联式-结构化-对齐版.xlsx'
    eq_val_df = pd.read_excel(eq_path,sheet_name='汇总表', engine='openpyxl')

    param_convert_df = pd.read_excel(eq_path,sheet_name='应用物性对齐', engine='openpyxl')
    param_convert_dict = {}
    for param_convert_index in param_convert_df.index:
        short_name = param_convert_df.at[param_convert_index,'ePropAbbreviation']
        sei_name = param_convert_df.at[param_convert_index,'eAppPropNameEN']
        param_convert_dict[short_name] = sei_name

    if DataBanks.objects.filter(dbId = 3).exists():
        db = DataBanks.objects.filter(dbId = 3).first()
    else:
        db = DataBanks.objects.create(dbId = 3,dbName = '数据集TDEP',dbDescription = 'PCdata温压关联式推荐数据集-NV',status = 1)
        db.save()

    # 提前加载 Compound 数据到内存
    compound_map = {comp.cpId: comp for comp in Compound.objects.all()}

    # 提前加载 Property 数据到内存
    property_map = {prop.propFullname: prop for prop in Property.objects.all()}

    # 提前加载 CompoundProperty 数据到内存
    # compound_property_map = {
    #     (cp.cpCompoundId.cpId, cp.cpPropertyId.propFullname): cp
    #     for cp in CompoundProperty.objects.select_related('cpCompoundId', 'cpPropertyId')
    # }
    compound_property_id_map = {
        (cp.cpCompoundId.cpId, cp.cpPropertyId.propFullname): cp.cpId
        for cp in CompoundProperty.objects.select_related('cpCompoundId', 'cpPropertyId')
    }
    compound_properties_to_create = []  # 用于批量创建 CompoundProperty 对象

    # 提前加载 DataBankCompounds 数据到内存
    # databank_compounds_map = {
    #     (dbc.dbcDataBankId.dbId, dbc.dbcCompoundPropId.cpId): dbc
    #     for dbc in DataBankCompounds.objects.select_related('dbcDataBankId', 'dbcCompoundPropId')
    # }
    databank_compounds_id_map={
        (dbc.dbcDataBankId.dbId, dbc.dbcCompoundPropId.cpId): dbc.dbcId
        for dbc in DataBankCompounds.objects.select_related('dbcDataBankId', 'dbcCompoundPropId')
    }
    databank_compounds_to_create = []  # 用于批量创建 DataBankCompounds 对象

    dbcpdatainfo_map = {
        (dbcpi.dbcpeId.dbcId): dbcpi
        for dbcpi in DbcpDataInfo.objects.select_related('dbcpeId')
    }
    dbcpdatainfo_to_create = []  # 用于批量创建 DbcpDataInfo 对象

    property_value_consts_map = {
        (pvc.pvcCompoundPropId.cpId, pvc.pvcLabeledValueId.lvId): pvc
        for pvc in PropertyValueConst.objects.select_related('pvcCompoundPropId', 'pvcLabeledValueId')
    }
    property_value_consts_to_create = []  # 用于批量创建 PropertyValueConst 对象

    # 提前加载 Literature 数据到内存
    literature_map = {lit.liter_code: lit for lit in Literature.objects.all()}

    for eq_val_df_index in tqdm(eq_val_df.index, desc="Processing eq_mng_df"):
        comp_sei_id = eq_val_df.at[eq_val_df_index, 'nPCdataCompID']
        if pd.isna(comp_sei_id) or comp_sei_id == '':
            continue
        comp_sei_id = int(comp_sei_id)

        # 使用内存中的 Compound 数据
        comp = compound_map.get(comp_sei_id)
        if not comp:
            continue

        prop_abb_name = eq_val_df.at[eq_val_df_index, 'ePropAbbreviation']
        propfullname = param_convert_dict.get(prop_abb_name, None)
        if not propfullname:
            print('未找到应用物性缩写:', prop_abb_name)
            continue

        # 使用内存中的 Property 数据
        param = property_map.get(propfullname)
        if not param:
            continue

        # 使用内存中的 CompoundProperty 数据
        cp_key = (comp.cpId, param.propFullname)
        cp_id = compound_property_id_map.get(cp_key)
        if not cp_id:
            max_cp_id = max(compound_property_id_map.values(), default=0) + 1
            new_cp = CompoundProperty(
                cpId=max_cp_id,
                cpCompoundId=comp,
                cpPropertyId=param,
                status=1
            )
            compound_properties_to_create.append(new_cp)
            compound_property_id_map[cp_key] = max_cp_id
        else:
            new_cp = CompoundProperty.objects.get(cpId=cp_id)
        # 使用内存中的 DataBankCompounds 数据
        dbc_key = (db.dbId, new_cp.cpId)
        dbc_id = databank_compounds_id_map.get(dbc_key)
        if not dbc_id:
            max_dbc_id = max(databank_compounds_id_map.values(), default=0) + 1
            new_dbcp = DataBankCompounds(
                dbcId=max_dbc_id,
                dbcDataBankId=db,
                dbcCompoundPropId=new_cp,
                status=1
            )
            if not pd.isna(eq_val_df.at[eq_val_df_index, 'Note']):
                new_dbcp.dbcNote = eq_val_df.at[eq_val_df_index, 'Note']
            databank_compounds_to_create.append(new_dbcp)
            databank_compounds_id_map[dbc_key] = max_dbc_id
        else:
            new_dbcp = DataBankCompounds.objects.get(dbcId=dbc_id)
        
        dbcpi_key = new_dbcp.dbcId
        # 使用内存中的 DbcpDataInfo 数据
        if dbcpi_key not in dbcpdatainfo_map:
            new_dbcpi = DbcpDataInfo(
                dbcpeId=new_dbcp,
                dbcpeErrorSource='TDEP-NV',
                status=1
            )
            literature_code = eq_val_df.at[eq_val_df_index, 'nPCdataCitID']
            if not pd.isna(literature_code) and literature_code != '':
                literature = literature_map.get(literature_code)
                if literature:
                    new_dbcpi.dbcpeLiterature = literature
            dbcpdatainfo_to_create.append(new_dbcpi)
            dbcpdatainfo_map[dbcpi_key] = new_dbcpi
        # else:
        #     new_dbcpi = dbcpdatainfo_map[dbcpi_key]
        #     literature_code = eq_val_df.at[eq_val_df_index, 'nPCdataCitID']
        #     if not pd.isna(literature_code) and literature_code != '':
        #         literature = literature_map.get(literature_code)
        #         if literature:
        #             new_dbcpi.dbcpeLiterature = literature
        #     dbcpdatainfo_to_create.append(new_dbcpi)
        #     dbcpdatainfo_map[dbcpi_key] = new_dbcpi

        prop_ele_list = param.propertyelements_set.all()
        for ele in prop_ele_list:
            lvij = ele.peLabeledValueIJ
            # print(lvij.lvLabel)
            ele_index = ele.peIndex+1
            par_str = 'PAR'+str(ele_index)
            pvc_value = eq_val_df.at[eq_val_df_index,par_str]
            # print(pvc_value,'!!!!!!!!!!')
            pvc_key = (new_cp.cpId, lvij.lvId)
            # 使用内存中的 PropertyValueConst 数据
            pvc_id = property_value_consts_map.get(pvc_key)
            if pvc_id:
                pass
            else:
                new_pvc = PropertyValueConst(
                    pvcCompoundPropId=new_cp,
                    pvcLabeledValueId=lvij,
                    pvcValue=pvc_value,
                    status=1
                )
                property_value_consts_to_create.append(new_pvc)
                property_value_consts_map[pvc_key] = new_pvc

        for i in range(1):
            var_label_str = 'Var'+str(i+1)+'Symbol'
            var_min_str = 'Var'+str(i+1)+'Min'
            var_max_str = 'Var'+str(i+1)+'Max'
            var_label = eq_val_df.at[eq_val_df_index,var_label_str]
            if pd.isna(var_label):
                break
            var_min_val = eq_val_df.at[eq_val_df_index,var_min_str]
            var_max_val = eq_val_df.at[eq_val_df_index,var_max_str]
            prop_var_name = propfullname+'_VAR'+str(i+1)

            var_prop = property_map.get(prop_var_name)
            if var_prop:
                # 使用内存中的 CompoundProperty 数据
                cp_key = (comp.cpId, var_prop.propFullname)
                cp_id = compound_property_id_map.get(cp_key)
                if not cp_id:
                    max_cp_id = max(compound_property_id_map.values(), default=0) + 1
                    new_cp = CompoundProperty(
                        cpId=max_cp_id,
                        cpCompoundId=comp,
                        cpPropertyId=var_prop,
                        status=1
                    )
                    compound_properties_to_create.append(new_cp)
                    compound_property_id_map[cp_key] = max_cp_id
                else:
                    new_cp = CompoundProperty.objects.get(cpId=cp_id)

                # 使用内存中的 DataBankCompounds 数据
                dbc_key = (db.dbId, new_cp.cpId)
                dbc_id = databank_compounds_id_map.get(dbc_key)
                if not dbc_id:
                    max_dbc_id = max(databank_compounds_id_map.values(), default=0) + 1
                    new_dbcp = DataBankCompounds(
                        dbcId=max_dbc_id,
                        dbcDataBankId=db,
                        dbcCompoundPropId=new_cp,
                        status=1
                    )
                    databank_compounds_to_create.append(new_dbcp)
                    databank_compounds_id_map[dbc_key] = max_dbc_id
                else:
                    new_dbcp = DataBankCompounds.objects.get(dbcId=dbc_id)

                var_ele_list = var_prop.propertyelements_set.all()
                for var_ele in var_ele_list:
                    lvij = var_ele.peLabeledValueIJ
                    pvc_key = (new_cp.cpId, lvij.lvId)
                    pvc_id = property_value_consts_map.get(pvc_key)
                    cur_value = None
                    if 'min' in lvij.lvLabel:
                        cur_value = var_min_val
                    elif 'max' in lvij.lvLabel:
                        cur_value = var_max_val
                    if pvc_id:
                        pass
                    else:
                        new_pvc = PropertyValueConst(
                            pvcCompoundPropId=new_cp,
                            pvcLabeledValueId=lvij,
                            pvcValue=cur_value,
                            status=1
                        )
                        property_value_consts_to_create.append(new_pvc)
                        property_value_consts_map[pvc_key] = new_pvc

            if len(compound_properties_to_create) >= 1000:
                batch_size = 1000
                
                # 批量创建 CompoundProperty 对象
                for i in range(0, len(compound_properties_to_create), batch_size):
                    with transaction.atomic():
                        CompoundProperty.objects.bulk_create(compound_properties_to_create[i:i+batch_size])
                compound_properties_to_create.clear()

                # 批量创建 DataBankCompounds 对象
                for i in range(0, len(databank_compounds_to_create), batch_size):
                    with transaction.atomic():
                        DataBankCompounds.objects.bulk_create(databank_compounds_to_create[i:i+batch_size])
                databank_compounds_to_create.clear()

                # 批量创建 DbcpDataInfo 对象
                for i in range(0, len(dbcpdatainfo_to_create), batch_size):
                    with transaction.atomic():
                        DbcpDataInfo.objects.bulk_create(dbcpdatainfo_to_create[i:i+batch_size])
                dbcpdatainfo_to_create.clear()

                # 批量创建 PropertyValueConst 对象
                for i in range(0, len(property_value_consts_to_create), batch_size):
                    with transaction.atomic():
                        PropertyValueConst.objects.bulk_create(property_value_consts_to_create[i:i+batch_size])
                property_value_consts_to_create.clear()
    # 批量创建 CompoundProperty 对象
    batch_size = 1000
    for i in range(0, len(compound_properties_to_create), batch_size):
        with transaction.atomic():
            CompoundProperty.objects.bulk_create(compound_properties_to_create[i:i+batch_size])
    
    # 批量创建 DataBankCompounds 对象
    for i in range(0, len(databank_compounds_to_create), batch_size):
        with transaction.atomic():
            DataBankCompounds.objects.bulk_create(databank_compounds_to_create[i:i+batch_size])
    
    # 批量创建 DbcpDataInfo 对象
    for i in range(0, len(dbcpdatainfo_to_create), batch_size):
        with transaction.atomic():
            DbcpDataInfo.objects.bulk_create(dbcpdatainfo_to_create[i:i+batch_size])
    
    
    # 批量创建 PropertyValueConst 对象
    for i in range(0, len(property_value_consts_to_create), batch_size):
        with transaction.atomic():
            PropertyValueConst.objects.bulk_create(property_value_consts_to_create[i:i+batch_size])

def read_constant_prop_value():
    eq_path = dippr_path+r'/A080B. PCdata基表-9-物理常数表（Physical Constants）（20250617R1）-1版.xlsx'
    eq_mng_df = pd.read_excel(eq_path,sheet_name='物理常数表（入库版）', engine='openpyxl',header=2)

    for eq_mng_index in tqdm(eq_mng_df.index, desc="Processing eq_mng_df"):
        const_type = eq_mng_df.at[eq_mng_index,'常数类型']
        const_sei_id = eq_mng_df.at[eq_mng_index,'常数编号']
        const_en_name = eq_mng_df.at[eq_mng_index,'常数英文名称']
        const_name = eq_mng_df.at[eq_mng_index,'常数中文名称']
        const_symbol = eq_mng_df.at[eq_mng_index,'常数符号']
        const_value = eq_mng_df.at[eq_mng_index,'常数数值']
        const_unit = eq_mng_df.at[eq_mng_index,'常数单位']
        const_re = eq_mng_df.at[eq_mng_index,'相对不确定度']

        if pd.isna(const_sei_id) or const_sei_id == '':
            continue
        const_sei_id = int(const_sei_id)
        if ConstantPropertyValue.objects.filter(cpvId = const_sei_id).exists():
            print('常数已存在:',const_sei_id)
            continue
        else:
            constant_prop = ConstantPropertyValue.objects.create(cpvId = const_sei_id,cpvConstantType = const_type
                                                                 ,cpvConstantName = const_name,cpvConstantNameEN = const_en_name
                                                ,cpvConstantSymbol = const_symbol,cpvConstantValue = const_value,cpvConstantUnits = const_unit
                                                ,cpvConstantRelativeUncertainty = const_re,status = 1)
            constant_prop.save()


from django.db import transaction
from django.db.models import Max
from collections import defaultdict
import pandas as pd
from tqdm import tqdm

def read_nistv140_tdep_prop_value_final():
    # 常量定义
    BATCH_SIZE = 5000  # 批量处理大小
    eq_path = nistv_path + r'/NISTV140-温度关联式-结构化-对齐版.xlsx'
    
    # 1. 数据预加载 - 最小化内存占用
    # 只加载必要字段
    compound_ids = set(Compound.objects.values_list('cpId', flat=True))
    properties = Property.objects.values('propId', 'propFullname')
    property_id_map = {prop['propFullname']: prop['propId'] for prop in properties}
    
    # 加载属性元素关系
    prop_elements = defaultdict(list)
    for pe in PropertyElements.objects.select_related('peLabeledValueIJ').values(
        'pePropertyId', 'peIndex', 'peLabeledValueIJ_id'
    ):
        prop_elements[pe['pePropertyId']].append({
            'index': pe['peIndex'],
            'lvId': pe['peLabeledValueIJ_id']
        })
    
    # 2. 主键预生成 - 避免冲突 (PropertyValueConst 不需要预生成主键)
    cp_max_id = CompoundProperty.objects.aggregate(max_id=Max('cpId'))['max_id'] or 0
    dbc_max_id = DataBankCompounds.objects.aggregate(max_id=Max('dbcId'))['max_id'] or 0
    dbcpi_max_id = DbcpDataInfo.objects.aggregate(max_id=Max('dbcpeId'))['max_id'] or 0
    
    # 3. 初始化映射字典
    compound_property_map = {}  # (compound_id, property_id) -> cpId
    databank_compounds_map = {}  # (db_id, cp_id) -> dbcId
    dbcpdatainfo_map = {}  # dbcId -> exists
    property_value_consts_map = set()  # 使用集合存储 (cpId, lvId) 组合，避免重复创建

    # 4. Excel数据读取
    # 只读取需要的列
    usecols = [
        'nPCdataCompID', 'ePropAbbreviation', 'Note', 'nPCdataCitID',
        'PAR1', 'PAR2', 'PAR3', 'PAR4', 'PAR5','PAR6', 'PAR7', 'PAR8', 'PAR9', 'PAR10','PAR11',
        'Var1Symbol', 'Var1Min', 'Var1Max'
    ]
    
    eq_val_df = pd.read_excel(
        eq_path, 
        sheet_name='汇总表', 
        engine='openpyxl',
        usecols=usecols,
        dtype={'nPCdataCompID': 'Int64', 'nPCdataCitID': 'str'}
    )
    
    # 提前过滤无效行
    eq_val_df = eq_val_df.dropna(subset=['nPCdataCompID'])
    eq_val_df = eq_val_df[eq_val_df['nPCdataCompID'] != '']
    
    # 参数转换字典
    param_convert_df = pd.read_excel(
        eq_path,
        sheet_name='应用物性对齐', 
        engine='openpyxl',
        usecols=['ePropAbbreviation', 'eAppPropNameEN']
    )
    param_convert_dict = param_convert_df.set_index('ePropAbbreviation')['eAppPropNameEN'].to_dict()
    
    # 5. 数据库对象准备
    db, created = DataBanks.objects.get_or_create(
        dbId=3,
        defaults={
            'dbName': '数据集TDEP',
            'dbDescription': 'PCdata温压关联式推荐数据集-NV',
            'status': 1
        }
    )
    
    # 6. 加载文献数据
    literature_map = {lit.liter_code: lit.liter_id for lit in Literature.objects.all()}
    
    # 7. 主处理循环
    compound_properties_to_create = []
    databank_compounds_to_create = []
    dbcpdatainfo_to_create = []
    property_value_consts_to_create = []
    
    # 进度条
    progress_bar = tqdm(eq_val_df.iterrows(), total=len(eq_val_df), desc="Processing NISTV140")
    
    for idx, row in progress_bar:
        comp_sei_id = row['nPCdataCompID']
        
        # 跳过无效化合物
        if pd.isna(comp_sei_id) or comp_sei_id not in compound_ids:
            continue
        
        prop_abb_name = row['ePropAbbreviation']
        if pd.isna(prop_abb_name):
            continue
            
        propfullname = param_convert_dict.get(prop_abb_name)
        
        # 跳过无效属性
        if not propfullname or propfullname not in property_id_map:
            continue
        
        prop_id = property_id_map[propfullname]
        
        # ===== 处理主属性 =====
        # 创建或获取CompoundProperty
        cp_key = (comp_sei_id, prop_id)
        cp_id = compound_property_map.get(cp_key)
        
        if not cp_id:
            cp_max_id += 1
            new_cp = CompoundProperty(
                cpId=cp_max_id,
                cpCompoundId_id=comp_sei_id,
                cpPropertyId_id=prop_id,
                status=1
            )
            compound_properties_to_create.append(new_cp)
            compound_property_map[cp_key] = cp_max_id
            cp_id = cp_max_id
        
        # 创建或获取DataBankCompounds
        dbc_key = (db.dbId, cp_id)
        dbc_id = databank_compounds_map.get(dbc_key)
        
        if not dbc_id:
            dbc_max_id += 1
            new_dbcp = DataBankCompounds(
                dbcId=dbc_max_id,
                dbcDataBankId_id=db.dbId,
                dbcCompoundPropId_id=cp_id,
                status=1,
                dbcNote=row['Note'] if not pd.isna(row.get('Note')) else None
            )
            databank_compounds_to_create.append(new_dbcp)
            databank_compounds_map[dbc_key] = dbc_max_id
            dbc_id = dbc_max_id
        
        # 创建DbcpDataInfo
        if dbc_id not in dbcpdatainfo_map:
            dbcpi_max_id += 1
            literature_code = row['nPCdataCitID']
            literature_id = literature_map.get(str(literature_code)) if not pd.isna(literature_code) else None
            
            new_dbcpi = DbcpDataInfo(
                dbcpeId=dbcpi_max_id,
                dbcpeDataBankCompoundsId_id=dbc_id,
                dbcpeErrorSource='TDEP-NV',
                status=1,
                dbcpeLiterature_id=literature_id
            )
            dbcpdatainfo_to_create.append(new_dbcpi)
            dbcpdatainfo_map[dbc_id] = True
        
        # 处理属性元素值 (PAR1-PAR5)
        elements = prop_elements.get(prop_id, [])
        for ele in elements:
            ele_index = ele['index'] + 1
            par_value = row.get(f'PAR{ele_index}')
            
            if pd.isna(par_value):
                continue
                
            pvc_key = (cp_id, ele['lvId'])
            
            # 使用集合检查唯一性
            if pvc_key not in property_value_consts_map:
                new_pvc = PropertyValueConst(
                    pvcCompoundPropId_id=cp_id,
                    pvcLabeledValueId_id=ele['lvId'],
                    pvcValue=par_value,
                    status=1
                )
                property_value_consts_to_create.append(new_pvc)
                property_value_consts_map.add(pvc_key)
        
        # ===== 处理变量属性 (Var1) =====
        var_label = row.get('Var1Symbol')
        if pd.isna(var_label):
            continue
            
        var_min_val = row.get('Var1Min')
        var_max_val = row.get('Var1Max')
        
        # 跳过无效变量值
        if pd.isna(var_min_val) or pd.isna(var_max_val):
            continue
            
        var_prop_name = f"{propfullname}_VAR1"
        var_prop_id = property_id_map.get(var_prop_name)
        
        if not var_prop_id:
            continue
            
        # 创建或获取变量CompoundProperty
        var_cp_key = (comp_sei_id, var_prop_id)
        var_cp_id = compound_property_map.get(var_cp_key)
        
        if not var_cp_id:
            cp_max_id += 1
            new_var_cp = CompoundProperty(
                cpId=cp_max_id,
                cpCompoundId_id=comp_sei_id,
                cpPropertyId_id=var_prop_id,
                status=1
            )
            compound_properties_to_create.append(new_var_cp)
            compound_property_map[var_cp_key] = cp_max_id
            var_cp_id = cp_max_id
        
        # 创建或获取变量DataBankCompounds
        var_dbc_key = (db.dbId, var_cp_id)
        var_dbc_id = databank_compounds_map.get(var_dbc_key)
        
        if not var_dbc_id:
            dbc_max_id += 1
            new_var_dbcp = DataBankCompounds(
                dbcId=dbc_max_id,
                dbcDataBankId_id=db.dbId,
                dbcCompoundPropId_id=var_cp_id,
                status=1
            )
            databank_compounds_to_create.append(new_var_dbcp)
            databank_compounds_map[var_dbc_key] = dbc_max_id
            var_dbc_id = dbc_max_id
        
        # 处理变量属性元素值
        var_elements = prop_elements.get(var_prop_id, [])
        for var_ele in var_elements:
            lv_id = var_ele['lvId']
            pvc_key = (var_cp_id, lv_id)
            
            # 根据标签确定值
            # 注意：这里需要根据实际逻辑确定值，简化处理
            if 'min' in var_prop_name.lower():
                cur_value = var_min_val
            elif 'max' in var_prop_name.lower():
                cur_value = var_max_val
            else:
                continue
                
            # 使用集合检查唯一性
            if pvc_key not in property_value_consts_map:
                new_pvc = PropertyValueConst(
                    pvcCompoundPropId_id=var_cp_id,
                    pvcLabeledValueId_id=lv_id,
                    pvcValue=cur_value,
                    status=1
                )
                property_value_consts_to_create.append(new_pvc)
                property_value_consts_map.add(pvc_key)
        
        # 批量提交
        if len(property_value_consts_to_create) >= BATCH_SIZE:
            with transaction.atomic():
                if compound_properties_to_create:
                    CompoundProperty.objects.bulk_create(compound_properties_to_create)
                if databank_compounds_to_create:
                    DataBankCompounds.objects.bulk_create(databank_compounds_to_create)
                if dbcpdatainfo_to_create:
                    DbcpDataInfo.objects.bulk_create(dbcpdatainfo_to_create)
                if property_value_consts_to_create:
                    PropertyValueConst.objects.bulk_create(property_value_consts_to_create)
            
            # 清空临时列表
            compound_properties_to_create = []
            databank_compounds_to_create = []
            dbcpdatainfo_to_create = []
            property_value_consts_to_create = []
            
            # 更新进度条
            progress_bar.set_postfix({
                'CP': cp_max_id, 
                'DBC': dbc_max_id,
                'PVC': len(property_value_consts_map)
            })
    
    # 最终批量提交
    with transaction.atomic():
        if compound_properties_to_create:
            CompoundProperty.objects.bulk_create(compound_properties_to_create, batch_size=BATCH_SIZE)
        if databank_compounds_to_create:
            DataBankCompounds.objects.bulk_create(databank_compounds_to_create, batch_size=BATCH_SIZE)
        if dbcpdatainfo_to_create:
            DbcpDataInfo.objects.bulk_create(dbcpdatainfo_to_create, batch_size=BATCH_SIZE)
        if property_value_consts_to_create:
            PropertyValueConst.objects.bulk_create(property_value_consts_to_create, batch_size=BATCH_SIZE)
    
    # 返回处理统计
    return {
        'compounds_processed': len(eq_val_df),
        'compound_properties_created': cp_max_id - (CompoundProperty.objects.aggregate(Max('cpId'))['cpId__max'] or 0),
        'property_values_created': len(property_value_consts_map)
    }